Hi Guys,
Need help. I am trying to Update table2 from table 1. Below is sample code. What I want
update Link_Table2.STATUS field with my Linking keys
Link-Table1.link1 = Link-Table2.link1
Link-Table2.link2 = Link-Table2.link2
If I get one record update Link-Table2.Status = 'Good'
If I get more than one records update Link-Table2.Status = 'Duplicate Rows'
If I do not find any link update Link-Table.Status = 'Not Found'
Any help would be great appreciated.
Thank You.
-- Create Sample Table
CREATE TABLE [dbo].[Link-Table1](
[Link1] VARCHAR NULL,
[Link2] VARCHAR NULL,
[fname] VARCHAR NULL,
[lname] VARCHAR NULL
) ON [PRIMARY]
GO;
CREATE TABLE [dbo].[Link-Table2](
[link1] VARCHAR NULL,
[link2] VARCHAR NULL,
[phone] NVARCHAR NULL,
[address] NVARCHAR NULL,
[status] NVARCHAR NULL
) ON [PRIMARY]
GO;
--Insert sample record into [Link-Table1]
INSERT INTO dbo.[Link-Table1]
( Link1, Link2, fname, lname )
VALUES ( 'BRAVO', -- Link1 - varchar(40)
'123456', -- Link2 - varchar(50)
'JON', -- fname - varchar(50)
'SMITH' -- lname - varchar(50)
);
INSERT INTO dbo.[Link-Table1]
( Link1, Link2, fname, lname )
VALUES ( 'CHILLER', -- Link1 - varchar(40)
'4199', -- Link2 - varchar(50)
'CHRIS', -- fname - varchar(50)
'SAM' -- lname - varchar(50)
);
INSERT INTO dbo.[Link-Table1]
( Link1, Link2, fname, lname )
VALUES ( 'USA', -- Link1 - varchar(40)
'2323', -- Link2 - varchar(50)
'NORMAN', -- fname - varchar(50)
'SMITH' -- lname - varchar(50)
);
INSERT INTO dbo.[Link-Table1]
( Link1, Link2, fname, lname )
VALUES ( 'BRAVO', -- Link1 - varchar(40)
'123456', -- Link2 - varchar(50)
'JON', -- fname - varchar(50)
'S' -- lname - varchar(50)
);
-- Insert records into [Link-Table2]
INSERT INTO dbo.[Link-Table2]
( link1, link2, phone, address )
VALUES ( 'BRAVO', -- link1 - varchar(50)
'123456', -- link2 - varchar(50)
'7405638899', -- phone - nvarchar(50)
'1487 North Street' -- address - nvarchar(50)
);
INSERT INTO dbo.[Link-Table2]
( link1, link2, phone, address )
VALUES ( 'USA', -- link1 - varchar(50)
'2323', -- link2 - varchar(50)
'7445638899', -- phone - nvarchar(50)
'1487 South Street' -- address - nvarchar(50)
);
INSERT INTO dbo.[Link-Table2]
( link1, link2, phone, address )
VALUES ( 'CA', -- link1 - varchar(50)
'2323234', -- link2 - varchar(50)
'8585638899', -- phone - nvarchar(50)
'4489 wEST Street' -- address - nvarchar(50)
);
SELECT * FROM dbo.Link_Table2
SELECT * FROM dbo.[Link-Table1]