CODEWITHSUNDEEP
sqlsql-serversql-server-2012sql-update
Amel
Amel

Reputation: 708

UPDATE one SQL table based on equality of two columns with two columns from another table

I'm using SQL Server 2012 and I have this problem.

I want to update Table1, column 3 (with a lot of duplicate rows) with values from Table 2, column 3 (c3), where Table1.c1 = Table2.c1 and Table1.c2 = Table2.c2 (Table2 contains explanation for every row, and Table1 has a lot more rows which are also duplicates).

Here's example

I tried this query, but it doesn't work:

UPDATE T1
SET c3 = T2.c3
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.c1 = T2.c1 AND T1.c2 = T2.c2

EDIT:

Query above is OK! I had some invisible strings in my columns. Thank you Xiaoguang Qiao

Upvotes: 0

Views: 930

Answers (1)

qxg
qxg

Reputation: 7036

I didn't see any problem with the query itself. First check data will be updated by selecting

SELECT T1.C3, T2.C3
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.c1 = T2.c1 AND T1.c2 = T2.c2

If it returns 0 rows, I think you need to check C1 and C2 one by one. Maybe there are invisible spaces in column C1 or C2. Try following query

SELECT T1.C1, T2.C1, CASE WHEN T1.C1 = T2.C1 THEN 'Same' ELSE NULL END AS "Same C1",
T1.C2, T2.C2, CASE WHEN T1.C2 = T2.C2 THEN 'Same' ELSE NULL END AS "Same C2"
FROM Table1 AS T1, Table2 T2

Upvotes: 2

Related Questions