October 27, 2016 at 11:42 am
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](40) NULL,
[Link2] [VARCHAR](50) NULL,
[fname] [VARCHAR](50) NULL,
[lname] [VARCHAR](50) NULL
) ON [PRIMARY]
GO;
CREATE TABLE [dbo].[Link-Table2](
[link1] [VARCHAR](50) NULL,
[link2] [VARCHAR](50) NULL,
[phone] [NVARCHAR](50) NULL,
[address] [NVARCHAR](50) NULL,
[status] [NVARCHAR](20) 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]
October 27, 2016 at 12:10 pm
you can do a JOIN in an UPDATE statement, and I think in this case you'd want to use a subquery to figure out the counts first:
UPDATE t2 SET
status = CASE WHEN t1.row_count = 1 THEN 'Good' WHEN t1.row_count > 1 THEN 'Duplicate Rows' ELSE 'Not Found' END
FROM dbo.[Link-Table2] t2
LEFT OUTER JOIN
(SELECT link1, link2, count(*) AS row_count FROM dbo.[Link-Table1] GROUP BY link1, link2) t1 ON t2.link1 = t1.Link1 AND t2.link2 = t1.Link2
October 27, 2016 at 12:32 pm
Thank You for your prompt reply.
However, I missed one part to mention.
The table structure of [Link-Table2] would be.
CREATE TABLE [dbo].[Link-Table2](
[link1] [VARCHAR](50) NULL,
[link2] [VARCHAR](50) NULL,
[phone] [NVARCHAR](50) NULL,
[address] [NVARCHAR](50) NULL,
[status] [NVARCHAR](20) NULL,
[fname] [VARCHAR](50) NULL,
[lname] [VARCHAR](50) NULL
) ON [PRIMARY]
GO
So basically I want to update Status,fname and lname on [Link-Table2].
Above SQL works great for update Status column.
If there is one match then update fname,lname and Status = 'Good'
if there is no link found then just update status = 'Not Found'
If there is more than one match then don't update fname, laname but update Status = 'Duplicate Records'
Any thoughts?
I really appreciate your help!
October 27, 2016 at 12:56 pm
Include the columns in the derived table by using MIN or MAX on them.
Then use a CASE clause to update only when the row_count = 1.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply