DB2 Update SQL WHERE clause containing subquery

Question

following situation:

I have 2 tables on a DB2 database:

orders

order_id order_date customer_id

stats

order_id status_code

By using a SELECT with JOIN I can get all of the orders with open status:

SELECT * FROM orders o LEFT JOIN stats s ON o.order_id = s.order_id 
WHERE s.status_code = 'open'

In a second SQL statement I would like to update the status_code for the orders which I just selected, like so:

UPDATE stats SET status_code = 'processing' WHERE status_code = 'open' 
AND order_id IN 
(SELECT o.order_id 
FROM orders o 
LEFT JOIN stats s 
ON s.order_id = o.order_id 
WHERE s.status_code = 'open')

At the update statement, I got the error "cursor state not valid". Is there a workaround or better solution for this?

Important: I need exact two statements, one for SELECT and the other for UPDATE. Each statement needs to be in one single line.

Thank you.

EDIT: New version of the update statement which is unfortunately still not working:

UPDATE stats SET status_code = 'processing' WHERE status_code = 'open' 
AND order_id IN 
(SELECT o.order_id 
FROM orders o  
WHERE s.status_code = 'open' AND s.order_id = o.order_id )

I have created an example: https://sqltest.net/#1124376

My goal is to use a second UPDATE statement changing the column 'status_code' into 'processing' for the 'order_id' from the first SELECT statement.

Please note that I do not find a DB2 sandbox that's why I just used MySQL on sqltest.net as an example to illustrate my requirement.

Does this answer your question? [SQL update from one Table to another based on a ID match IN db2](https://stackoverflow.com/questions/23285136/sql-update-from-one-table-to-another-based-on-a-id-match-in-db2) — mustaccio, Nov 05 '20 at 14:17
_Each statement needs to be in one single line._? Properly formatted SQL is so much easier to read, and to write. — jarlh, Nov 05 '20 at 14:55
Tip of today: Chose table aliases that make sense, like `o` for `orders` and `s` for `status`. — jarlh, Nov 05 '20 at 14:56
@mustaccio no not really. I need a subquery in the WHERE clause but on the post you mentioned it is a subquery in the SET clause. — perfectCircle1981, Nov 05 '20 at 14:58
It still shows you how to correlate the subquery to the table being updated. — mustaccio, Nov 05 '20 at 15:07
@mustaccio ok I tried to change the statement according to your reply but it is still not working. Pls check my edited SQL statement in my original post. — perfectCircle1981, Nov 05 '20 at 15:51
If you posted your exact code change `LEFT JOIN status` to `LEFT JOIN stats`. — forpas, Nov 05 '20 at 16:11
@forpas thx that's a typo only in my post. All of the tables and columns are fictionally renamed for simplification — perfectCircle1981, Nov 05 '20 at 16:36
@perfectCircle1981 then your query runs fine: https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=c852911b7bdac89b09cd8d952d8e0bb0 If you run the query in a programming language like Java, the error you get is a Java error. — forpas, Nov 05 '20 at 16:42

DB2 Update SQL WHERE clause containing subquery

0 Answers0