# Bug with "UPDATE" statement using subselect
**URL:** https://community.cratedb.com/t/bug-with-update-statement-using-subselect/1684
**Category:** CrateDB
**Created:** [December 29, 2023, 5:59am UTC](https://community.cratedb.com/t/bug-with-update-statement-using-subselect/1684 "2023-12-29T05:59:44Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@Jiren](https://community.cratedb.com/u/Jiren)
#### Post date: [December 29, 2023, 5:59am UTC](https://community.cratedb.com/t/bug-with-update-statement-using-subselect/1684/1 "2023-12-29T05:59:45Z")
So, this query:
```auto
UPDATE table
SET column = '123'
WHERE id = 2
AND NOT EXISTS (
SELECT 1
FROM table
WHERE column = '123'
);
```
Should NOT work if there is a row with column value 123 in the table already and YET it works and changes the row.
Huge problem for me so fix it as soon as possible.
---
### Author:  [@hammerhead](https://community.cratedb.com/u/hammerhead)
#### Post date: [December 29, 2023, 7:10am UTC](https://community.cratedb.com/t/bug-with-update-statement-using-subselect/1684/2 "2023-12-29T07:10:52Z")
Hi @Jiren,
Thanks for reporting. Would you mind opening an issue on GitHub ([Issues · crate/crate · GitHub](https://github.com/crate/crate/issues)), so our development team can interact with you directly?
Thanks.
---
### Author:  [@Jiren](https://community.cratedb.com/u/Jiren)
#### Post date: [December 29, 2023, 7:58am UTC](https://community.cratedb.com/t/bug-with-update-statement-using-subselect/1684/4 "2023-12-29T07:58:33Z")
What is there to interact with? I literally gave you the code and described the contradiction in its function. Fix it.
---
### Author:  [@proddata](https://community.cratedb.com/u/proddata)
#### Post date: [December 29, 2023, 8:35am UTC](https://community.cratedb.com/t/bug-with-update-statement-using-subselect/1684/5 "2023-12-29T08:35:21Z")
Dear @Jiren
First of all, welcome to the CrateDB Community 👋
May I remind you to take a look at [FAQ - CrateDB Community](https://community.cratedb.com/faq).
While [Crate.io](http://Crate.io) is a company, the community is a place for a civilised discussion around CrateDB for **everyone**. Also CrateDB itself is open source software under Apache License v2.0. Which implies that we are open to feedback **and** contributions to make it a better software, however it also specifically states, that we provide the software
> _“AS IS”,_ _WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND_
> Huge problem for me so fix it as soon as possible.
While we typically don’t discriminate on bugs in any way, if you need a speedy bug fix for a specific problem, I’d typically recommend to get in a commercial agreement.
* * *
Unfortunately you also left out one very important detail in your example, specifically that you defined `id` as `PRIMARY KEY`. Considering that - and while I see this as a bug - I would not consider this “HUGE”, as with `WHERE id = 2` you specifically select on one record, and one record only.
---
### Author:  [@hammerhead](https://community.cratedb.com/u/hammerhead)
#### Post date: [December 29, 2023, 8:54am UTC](https://community.cratedb.com/t/bug-with-update-statement-using-subselect/1684/6 "2023-12-29T08:54:50Z")
To elaborate more on the request for raising a GitHub issue: On opening a new issue, you will see a form with several input fields. This structure helps to process issues in a standardized way, such as having complete steps to reproduce. As @proddata already mentioned, your issue depends on the details of your `CREATE TABLE` statement (primary key definition), which wasn’t shared.
Additionally, you will receive direct updates from the development team when a fix is available, as well as potentially additional information, like applicable workarounds. I find it a fair ask to raise a GitHub issue in return for that.
---
### Author:  [@hernanc](https://community.cratedb.com/u/hernanc)
#### Post date: [December 29, 2023, 8:56am UTC](https://community.cratedb.com/t/bug-with-update-statement-using-subselect/1684/7 "2023-12-29T08:56:04Z")
Also, FWIW, while waiting for a fix you may want to try the following workaround which seems to work for me on 5.5.2:
```sql
INSERT INTO f2 (id,column1)
SELECT id
,CASE WHEN NOT EXISTS (
SELECT 1
FROM f2
WHERE column1 = '123'
)
THEN '123'
ELSE column1
END
FROM f2
WHERE id = 2
ON CONFLICT(id) DO UPDATE SET column1 = excluded.column1;
```
---
### Author:  [@hernanc](https://community.cratedb.com/u/hernanc)
#### Post date: [January 5, 2024, 8:34am UTC](https://community.cratedb.com/t/bug-with-update-statement-using-subselect/1684/8 "2024-01-05T08:34:24Z")
To ensure this is properly tracked I went ahead and raised it on GH → [UPDATE statement not honouring WHERE NOT EXISTS filter when used in conjunction with a filter on PK · Issue #15282 · crate/crate · GitHub](https://github.com/crate/crate/issues/15282)
---
### Author:  [@hernanc](https://community.cratedb.com/u/hernanc)
#### Post date: [January 8, 2024, 4:52pm UTC](https://community.cratedb.com/t/bug-with-update-statement-using-subselect/1684/9 "2024-01-08T16:52:27Z")
Hi again @Jiren , just a quick message to let you know that a fix for this issue has now been committed
[Fix handling of query conditions in delete/update with WHERE doc-keys by mfussenegger · Pull Request #15293 · crate/crate (github.com)](https://github.com/crate/crate/pull/15293)
---
### Author:  [@Jiren](https://community.cratedb.com/u/Jiren)
#### Post date: [January 9, 2024, 9:15am UTC](https://community.cratedb.com/t/bug-with-update-statement-using-subselect/1684/10 "2024-01-09T09:15:29Z")
Sure, but if I get the last update 5.5.2, will this bug be fixed or you are only planning to release the fixed version?
---
### Author:  [@hernanc](https://community.cratedb.com/u/hernanc)
#### Post date: [January 9, 2024, 9:33am UTC](https://community.cratedb.com/t/bug-with-update-statement-using-subselect/1684/11 "2024-01-09T09:33:43Z")
Hi,
This will be available on the next release which is coming soon, but in the meanwhile you can see this working already on the latest nightly build (docker image: `crate/crate:nightly-5.6.0-2024-01-09-00-02`)