What is the SQL syntax for joining two tables, comparing and updating?

Question

I'm attempting to update a single column in one table based on information from another but only if they are not already the same. The beginning part of the code below (up until case) is what I've got that works so far, but I can't seem to get the syntax right to compare before the update.

UPDATE table1
SET table.column1 = table2.column2
 FROM table1
inner join table2 ON
    table1.KEY = table2.KEY
    WHERE column4 = something and DATE between '10/12/14' and '10/15/14'
 CASE 
    WHEN table1.column1 != table2.column2
end;

I'm using SQL server 2008r2 but any SQL-compatible code is great too. Thanks.

It's the compare part that was throwing me for a loop. Not the straight update with joining tables. That's why the other question wasn't quite right.

`where ... case` doesn't make much sense... why even have a case? `where ... AND t1.c1 = t2.c2`? — Marc B, Apr 06 '15 at 21:01
possible duplicate of [Update a table using JOIN in SQL Server?](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server) — Barett, Apr 06 '15 at 21:08

score 1 · Accepted Answer · answered Apr 06 '15 at 21:05

1

With regard to the code in question, I think you needed something like this:

UPDATE table1
SET table.column1 = table2.column2
FROM table1
inner join table2 ON table1.KEY = table2.KEY
           and table1.column4 = something 
           and table1.DATE between '10/12/14' and '10/15/14'
           and table1.column1 != table2.column2

answered Apr 06 '15 at 21:05

void

7,760
3
25
43

Thanks! This worked... I knew it was something simple I just couldn't see. – Green Apr 06 '15 at 21:17

score 1 · Answer 2 · edited Jul 29 '20 at 18:42

UPDATE t1
SET t1.column1 = t2.column2
 FROM table1 t1
inner join table2 t2 ON t1.[KEY] = t2.[KEY]
WHERE column4 = 'something' 
AND [DATE] >= '20141012'   --<-- Avoid using between operator for dates
AND [DATE] <= '20141015'   --<-- Also use ASNI date format YYYYMMDD (Sargable)
AND t1.column1 <> t2.column2

The reason why you avoid using Between operator for date values read this What do BETWEEN and the devil have in common?

What is the SQL syntax for joining two tables, comparing and updating?

2 Answers2