Update using joins

dinjo_jo · #1 (**permalink**) 12-22-09, 05:38

I need to perform a update but not able to figure out in DB2

Code:

update TABLE A 
set B.COL = 6794
from TABLE B
where A.id = B.id 
and B.NAME = 'ABC'
and date(B.STARTDATE) = date (current date)

kandrusatish · #2 (**permalink**) 12-22-09, 06:17

Try this

update TABLE B
set B.COL = 6794
where B.id IN (select id from TABLE A)
and B.NAME = 'ABC'
and date(B.STARTDATE) = date (getdate())

dinjo_jo · #3 (**permalink**) 12-22-09, 06:21

What ? I said table a needs to be updated ?

Stealth_DBA · #4 (**permalink**) 12-22-09, 06:57

dinjo jo, Your statement is confusing, You have UPDATE TABLE A but it is followed by SET B.COL = 6794. It is not easy to tell which Table you were trying to Update.

dav1mo · #5 (**permalink**) 12-22-09, 10:31

either way, you would still write your sql in the manner above just switch your tables around.
Dave

przytula_guy · #6 (**permalink**) 12-22-09, 12:51

you have lost the join condition - you take the complete table a

tonkuma · #7 (**permalink**) 12-22-09, 17:54

Following to the syntax of UPDATE statement,
"update TABLE A ..." means that you want to update a table named "TABLE" and rename it A for referencing the table later in the statement.

It would be better to show example(sample data of tables before and after updated) to describe what you want to do.

kandrusatish · #8 (**permalink**) 12-22-09, 23:28

i assume the following code is what you are expecting

update TABLE A
set A.COL = 6794

where A.id IN

(select id from TABLE B
where B.NAME = 'ABC'
and date(B.STARTDATE) = date (getdate())
)

And if you are sure that the ID column is having unique values, then you can replace the operator 'IN' with '=' in my code, otherwise use the code as it is

Internet Services

Web Development

Digital Marketing

Consumer Tech