Help: UPDATE FIELD WHERE CONDITION BASED ON SELECT QUERY FROM MULTIPLE TABLES.

[MARIEDB2]

Hello,

I am struggling to build a query on a DB2 db wich does an update for multiple fields which are the result of a select query with where clause (using multiple tables in the condition).

E.g.
UPDATE TABLE_HDR
SET HDRFIELD1 = 'XX'
WHERE EXIST
(SELECT HDRFIELD1 FROM TABLE_HDR,TABLE_1A,TABLE_1B WHERE FIELD_1A= FIELD_1B AND HDRFIELD1=FIELD1A)

I always get the same result where it ignores my select query and updates HDRFIELD1 for all rows.

What I basically want is that it only updates HDRFIELD1 for the ROWS returned in the SELECT statement.

e.g. SELECT produces 25 rows then it should only update HDRFIELD1='XX' for those 25 rows.

Any ideas?
MARIEDB2

[spider007]

the reason it is updating all the rows is that because you have mentioned EXISTS in the wehere clause. So even if one rows exists, it will update all rows. You may want to try:

UPDATE TABLE_HDR
SET HDRFIELD1 = 'XX'
WHERE HDRFIELD1 IN (SELECT HDRFIELD1 FROM TABLE_HDR,TABLE_1A,TABLE_1B WHERE FIELD_1A= FIELD_1B AND HDRFIELD1=FIELD1A)

this is not performance tuned at all.

[MARIEDB2]

Hi,

Thanks for your reply.

When I try to run the query as per your above example I get an error SQL0412 "Subquery with more then one result column not valid."

UPDATE TABLE_HDR
SET HDRFIELD1 = 'XX'
WHERE HDRFIELD1 IN (SELECT HDRFIELD1 FROM TABLE_HDR,TABLE_1A,TABLE_1B WHERE FIELD_1A= FIELD_1B AND HDRFIELD1=FIELD1A)

(result set of the select is 21 rows for which I want to update HDRFIELD1='a_value')

Also you mentioned this is not performance tuned. This concerns me. How can it run more efficiently?

Any other suggestions that might work?

Thank you so much!

[MARIEDB2]

Sorry I need to correct something in my previous post!

Due to my select statement containing multiple columns it was giving me the error.
I have retested by using the select statement, selecting only that column. After that it ran but it updated all fields in the table rather then just the fields for which we ran a select.

Back to square one -((

Could it be at all related to the fact I am running it with razor?

[r035198x]

..
I have retested by using the select statement, selecting only that column. After that it ran but it updated all fields in the table rather then just the fields for which we ran a select.
...

Shouldn't do that. Let's see the code that you ran this time.

[MARIEDB2]

Shouldn't do that. Let's see the code that you ran this time.

Thank you all for your reply the previous sql statement worked!

Many thanks for your responses!