CODEWITHSUNDEEP
sql-server
Conrad Jagger
Conrad Jagger

Reputation: 643

SQL Server Query - Lookup from one table to another

Can anyone please help - See below two tables

Table - Master
Field 1 - Id
Field 2 - Name
Field 3 - Address
Field 4 - Effective End Date
Filed 5 - Last updated date

Table - Child
Field 1 - Id
Field 2 - Name
Field 3 - Address

I need to do lookup from Parent in child and if Parent record doesn't exist (based on Id, Name and Address) then update the effective end date and last updated date of Table Master.

New to SQL, so struggling with this basic query.

Regards

Upvotes: 0

Views: 764

Answers (3)

Ivan Golović
Ivan Golović

Reputation: 8832

Try it like this:

UPDATE  m
SET     [Effective End Date] = GETDATE()
        , [Last updated date] = GETDATE()
FROM    Master m
WHERE   NOT EXISTS
(
    SELECT  *
    FROM    Child c 
    WHERE   c.Id = m.Id
    AND     c.Name = m.Name
    AND     c.Address = m.Address
)

If you want to update records in Master when a record with same Id, Name, Address does not exist in Child table you can do it like this. There's also an OUTPUT clause that returns rows affected by the update. The dates are being updated to today's date but you can set that to anything you need.

Here is an SQL Fiddle

Upvotes: 2

Fahad Hussain
Fahad Hussain

Reputation: 1185

Try This

Update Master  Set [Effective End Date] = GETDATE(), 
[Last updated date] = GETDATE()
Where id  not in ( select distinct id from Child)

Simple

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460018

I need to do lookup from Parent in child and if Parent record doesn't exist (based on Id, Name and Address) then update the effective end date and last updated date of Table Master.

Are you looking for IF and EXISTS?

IF EXISTS(
  SELECT 1 FROM Child c
  WHERE Id = @Id
  AND   Name = @Name
  AND   Address = @Address
) 
BEGIN
    UPDATE Master 
    SET [Effective End Date] = @EffectiveEndDate
        [Last updated date]  = @LastUpdatedDate
    WHERE Id = @Id
    AND   Name = @Name
    AND   Address = @Address 
END

Upvotes: 1

Related Questions