CODEWITHSUNDEEP
sqlsql-server
tapiwa makamure
tapiwa makamure

Reputation: 3

Update a column after select where does not exists

enter image description here

How can i update a column after i have selected records which do not exist from another table. Need to update Status column to 0 when there are certain records which do not exist.

SELECT *
FROM [BankingServicesReconciliations].[dbo].[Test1] p1
WHERE p1.[Age] is  not null and
      Not EXISTS  (SELECT * FROM [BankingServicesReconciliations].[dbo].[Test2]  p2   WHERE ( p1.[Surname] =p2.[Surname]) )

Upvotes: 0

Views: 145

Answers (2)

Dejan Dozet
Dejan Dozet

Reputation: 1009

I prefer LEFT JOIN:

UPDATE p1
SET STATUS = 0
FROM [BankingServicesReconciliations].[dbo].[Test1] p1 LEFT JOIN
   [BankingServicesReconciliations].[dbo].[Test2] p2 ON p1.[Surname] = p2.[Surname] and p2.[Age] IS NOT NULL
WHERE p1.[Age] IS NOT NULL
  AND p2.[Surname] IS NULL

This one is way much cleaner and understandable than EXISTS approach and, you better believe me, is WAY MUCH FASTER!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Your code basically translates to an update:

UPDATE [BankingServicesReconciliations].[dbo].Test1
    SET status = 0
    WHERE Age is  not null and
          NOT EXISTS (SELECT 1
                      FROM [BankingServicesReconciliations].[dbo].[Test2] p2
                      WHERE p2.[Surname] = Test1.[Surname]
                     );

The only subtlety is that SQL Server doesn't allow you to declare an alias in the UPDATE clause. You could use a FROM clause but that hardly seems necessary in this case.

Upvotes: 1

Related Questions