need help with multi-table update

amy7756 · #1 (**permalink**) 03-07-07, 15:04

Hey All -

I need to update a table from data in another table. IE:

table A:

name, date

table B:

name, date

I need to update all the dates in table b from table a where a.name=b.name.

Thanks in advance!

Amy

stolze · #2 (**permalink**) 03-08-07, 07:51

Code:

UPDATE b
SET b.date = ( SELECT date FROM a WHERE b.name = a.name )
WHERE EXISTS ( SELECT date FROM a WHERE b.name = a.name )

Note that there must be at most one tuple in table A for each tuple in table B. Otherwise, you will have to specify which row in A should be used to update a row in B.

Specific products may have other facilities like MERGE statements or the ability to update something based on an updatable result set. For example:

Code:

UPDATE ( SELECT b.name, b.date, ( SELECT a.date FROM a WHERE a.name = b.name ) AS a_date FROM b )
SET date = a_date
WHERE a_date IS NOT NULL

gvee · #3 (**permalink**) 03-08-07, 07:57

Here's your first hint

Let me know how you get on!

Code:

UPDATE table-name SET column-name = {expression} [, column-name = {expression}] WHERE search-condition

r937 · #4 (**permalink**) 03-08-07, 08:35

amy, please do mention which database system you're using

the update SQL is different from one to the next

Internet Services

Web Development

Digital Marketing

Consumer Tech