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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy