CODEWITHSUNDEEP
sqlsql-server
Immortal
Immortal

Reputation: 1180

Comparing values in two SQL Server tables then update

I have 2 database tables and I want to update the first table based on matches between their common columns. Here is my code:

UPDATE TestingTool_WeeklyReports
SET InReleasenotes  = CASE WHEN u.functionname IS NULL THEN 'NO' ELSE 'YES' END
FROM TestingTool_WeeklyReports T1
OUTER APPLY (SELECT t2.functionname
             FROM TestDS_DSReleaseNotes T2
             WHERE T1.Datasourcename = t2.functionname) u

My code doesn't seem to give me expected results, can someone tell me if there is a better way of doing what I want to achieve?

Upvotes: 0

Views: 65

Answers (2)

etsa
etsa

Reputation: 5060

I think you can write a simple UPDATE like this (no need to use CTE). In this format you can comment UPDATE line and use a SELECT to view the results for testing:

UPDATE A SET InReleasenotes = CASE WHEN B.functionname IS NULL THEN 'N' ELSE 'Y' END
/* SELECT * */
FROM TestingTool_WeeklyReports A
LEFT JOIN  TestDS_DSReleaseNotes B ON A.Datasourcename = B.functionname;

Sample data:

CREATE TABLE TestingTool_WeeklyReports (Datasourcename VARCHAR(20), InReleasenotes VARCHAR(1))
INSERT INTO TestingTool_WeeklyReports VALUES ('AAA',NULL)
INSERT INTO TestingTool_WeeklyReports VALUES ('BBB',NULL)
CREATE TABLE  TestDS_DSReleaseNotes (functionname VARCHAR(20))
INSERT INTO TestDS_DSReleaseNotes VALUES ('AAA');

Output after update:

Datasourcename  InReleasenotes
AAA             Y
BBB             N

Upvotes: 0

Abdulkadir Erkmen
Abdulkadir Erkmen

Reputation: 202

You can use common table, then update. After updating matching columns you can update rest of unmatched columns by Update ... Where IS NULL

;WITH T AS(
    Select  * FROM TestingTool_WeeklyReports T1
    INNER JOIN TestDS_DSReleaseNotes T2  ON T1.Datasourcename = t2.functionname)
Update T SET InReleasenotes = 'YES'

Upvotes: 2

Related Questions