CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2sql-update
Earth
Earth

Reputation: 3571

How to update the records in a table based on comparing with the id column from another table?

Both the tables (Table1, Table2) has id columns (T1id, T2id)

From Table1, I am selecting some records based on condition(used where clause). Now, I will have some records which will have ids.

My goal is to Update those selected records for the ids (T1id column in Table1) which are not present in the ids (T2id column in Table2)

I tried the following query.

UPDATE [dbo].[Table1]  
     SET  
     Number = '', StartDate = NULL  
     WHERE
     T1id IN(SELECT T1id FROM [dbo].[Table1] WHERE DATEDIFF(hour, StartDate, DATEADD(MINUTE, 330, GETUTCDATE())) > 7)

How to include the T2id column of Table2 for comparing with T1id column of Table1 and update only the records of Table1 if T1id is not present in T2id in the above query? Thanks.

Upvotes: 1

Views: 273

Answers (2)

knkarthick24
knkarthick24

Reputation: 3216

USE Except operator while checking for ID in other table(Table 2):

UPDATE [dbo].[Table1]
SET    Number = '',
       StartDate = NULL
WHERE  T1id IN(SELECT T1id
               FROM   [dbo].[Table1]
               --WHERE  Datediff(hour, StartDate, Dateadd(MINUTE, 330, Getutcdate())) > 7) <--Your where cond goes here. 
               EXCEPT
               SELECT T2id
               FROM   [dbo].[Table2])

Upvotes: 0

jazzytomato
jazzytomato

Reputation: 7214

You can use joins in an update statement, so you can do something like this :

UPDATE  T1
     SET  
     Number = '', StartDate = NULL 
FROM [dbo].[Table1] T1
LEFT OUTER JOIN Table2 T2 ON T1.id = T2.id
     WHERE T2.id IS NULL 
     AND  DATEDIFF(hour, T1.StartDate, DATEADD(MINUTE, 330, GETUTCDATE())) > 7)

Upvotes: 3

Related Questions