Update Query help in T-SQL

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]

UPDATE lt2
SET status = CASE Link1_Count WHEN 0 THEN 'Not Found' WHEN 1 THEN 'Good' ELSE 'Duplicate Rows' END
FROM dbo.[Link-Table2] lt2
OUTER APPLY (
    SELECT TOP (2) COUNT(*) AS Link1_Count
    FROM dbo.[Link-Table1] lt1
    WHERE lt1.Link1 = lt2.Link1 AND
        lt1.Link2 = lt2.Link2
) AS oa1

Scott, I really appreciate 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 NULL,
[link2] VARCHAR NULL,
[phone] NVARCHAR NULL,
[address] NVARCHAR NULL,
[status] NVARCHAR NULL,
[fname] VARCHAR NULL,
[lname] VARCHAR 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!