Trying to update Table with data from another table

Question

I have 2 tables with a date field that doesn't match that need to.

First I have a query to return a list of records that all need the Date field updated.

SELECT distinct ID_NUMBER 
FROM a
INNER JOIN b
ON a.ID_NUMBER = b.ID_NUMBER
INNER JOIN c
ON c.ID_NUMBER = b.ID_NUMBER
INNER JOIN d
ON c.ID_NUMBER = d.ID_NUMBER
WHERE d.DATE <> b.DATE

returns 20K plus ID_NUMBER's

I want to update table B where the ID_Number = the list of ID_numbers from that query.

trying:

UPDATE b
SET d.DATE = b.DATE
Where (select distinct ID_NUMBER 
from a
INNER JOIN b
ON a.ID_NUMBER = b.ID_NUMBER
INNER JOIN c
ON c.ID_NUMBER = b.ID_NUMBER
INNER JOIN d
ON c.ID_NUMBER = d.ID_NUMBER
Where d.DATE <> b.DATE
)

should be straightforward. Can't seem to get the syntax. Help appreciated.

possible duplicate of [SQL update from one Table to another based on a ID match](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) — Kirk Broadhurst, Aug 30 '13 at 17:52
Hi user123456, thanks for your question. If you search the site you'll find similar questions to this. Also note that you haven't included any description of the error you receive, or what is actually happening when you run your SQL. Finally you should note that `UPDATE b, SET d.DATE = b.DATE` is incorrect - it should probably read `b.DATE = d.DATE` — Kirk Broadhurst, Aug 30 '13 at 17:56

score 1 · Answer 1 · answered Aug 30 '13 at 18:27

Since they all have the same ID_NUMBER and you're only looking at tables b and d anyway, couldn't you just do this?

UPDATE b
SET b.DATE = d.DATE
FROM b INNER JOIN d ON b.ID_NUMBER = d.ID_NUMBER
WHERE d.DATE <> b.DATE
  AND d.ID_NUMBER = b.ID_NUMBER;

SQL Fiddle link: http://sqlfiddle.com/#!3/a3af9/3

score 0 · Answer 2 · answered Aug 30 '13 at 18:07

0

I was able make this update query work. You can view it with some example data on SQL Fiddle.

UPDATE b
SET b.DATE = d.DATE
FROM a 
INNER JOIN b ON a.ID_NUMBER = b.ID_NUMBER
INNER JOIN c ON c.ID_NUMBER = b.ID_NUMBER
INNER JOIN d ON c.ID_NUMBER = d.ID_NUMBER
WHERE d.DATE <> b.DATE

Note: This is similar to @Sonam's answer, but the SET clause needs to set the b.DATE column instead of the d.DATE column because table b is being updated.

answered Aug 30 '13 at 18:07

Adam Porad

14,193
3
31
56

I did look for other solutions. Sorry. Thanks for your quick reply. I get this error when doing the code you sent: The multi-part identifier "b.DATE" could not be bound. – user2733794 Aug 30 '13 at 18:41
what version of SQL Server are you using? – Adam Porad Aug 30 '13 at 19:12

score -1 · Answer 3 · answered Aug 30 '13 at 17:53

-1

UPDATE b 
SET d.DATE = b.DATE 
from a INNER JOIN b 
ON a.ID_NUMBER = b.ID_NUMBER 
INNER JOIN c ON c.ID_NUMBER = b.ID_NUMBER 
INNER JOIN d ON c.ID_NUMBER = d.ID_NUMBER 
Where d.DATE <> b.DATE

answered Aug 30 '13 at 17:53

Sonam

3,406
1
12
24

Trying to update Table with data from another table

3 Answers3