Stuck on new fault with my update

alan_lynch (3/7/2013)

right Lynn I will try again.

I have had a look at your merge statement which has been useful the only thing is i have one error from the query below.

Please bare in mind it works fine as it is, but when I add a duplicate row to the Source(BigTable) for my test data-- like so ('Microsoft Office 2003 Professional'),

I get the error message

Msg 8672, Level 16, State 1, Line 9
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

in my production data I will have many many rows the same from the source table but they are to be unique in the lookuptable

drop table BigTable --Drop SOURCE
drop table RefTable --Drop Lookup

--Create a Source table
CREATE TABLE BigTable
(
software_name_raw VARCHAR(255)
)
GO
--Insert records into Source table
INSERT INTO BigTable
VALUES
('Microsoft Office 2003'),
('Microsoft 2003 Office'),
('Microsoft Office 2003 Professional'),
('Sun Microsystems')

GO
--Create LOOKUP table
CREATE TABLE RefTable
(
software_name_raw VARCHAR(255) PRIMARY KEY,
software_name_amended Varchar (255)
)
GO
--Insert records into Lookup table
INSERT INTO RefTable
VALUES
('Microsoft Office 2003', 'Office 2003'),
('Microsoft 2003 Office', 'Office 2003'),
('Microsoft Office 2003 Professional', 'Office 2003'),
('Adobe', 'Adobe Inc')
GO
SELECT * FROM BigTable as Source_TABLE
SELECT * FROM RefTable as Lookup_TABLE
GO

--Next I will use the MERGE SQL command to synchronize the target table
--with the refreshed data coming from the LOOKUP table.

--MERGE SQL statement – Part 2

--Synchronize the lookup table with
--refreshed data from source table
MERGE RefTable AS TARGET
USING BigTable AS SOURCE
ON (TARGET.software_name_raw = SOURCE.software_name_raw)

--When records are matched, update the records if there is any change

WHEN MATCHED AND TARGET.software_name_raw = SOURCE.software_name_raw THEN
UPDATE SET TARGET.software_name_raw = SOURCE.software_name_raw

--When no records are matched, insert the incoming records from source into the lookup table

WHEN NOT MATCHED BY TARGET THEN
INSERT (software_name_raw, software_name_amended)
VALUES (SOURCE.software_name_raw, 'Needs Updating')

--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: ‘INSERT’, ‘UPDATE’, or ‘DELETE’,
--according to the action that was performed on that row
OUTPUT $action,
DELETED.software_name_raw AS Lookupsoftware_name_raw,
DELETED.software_name_amended AS Lookupoftware_name_amended,
INSERTED.software_name_raw AS Sourcesoftware_name_raw,
INSERTED.software_name_amended AS Sourcesoftware_name_amended;
GO

select * from BigTable as source_table
select * from RefTable as lookuptable

drop table BigTable --Drop SOURCE
drop table RefTable --Drop Lookup

--Create a Source table
CREATE TABLE BigTable
(
software_name_raw VARCHAR(255)
)
GO
--Insert records into Source table
INSERT INTO BigTable
VALUES
('Microsoft Office 2003'),
('Microsoft 2003 Office'),
('Microsoft Office 2003 Professional'),
('Sun Microsystems')

GO
--Create LOOKUP table
CREATE TABLE RefTable
(
software_name_raw VARCHAR(255) PRIMARY KEY,
software_name_amended Varchar (255)
)
GO
--Insert records into Lookup table
INSERT INTO RefTable
VALUES
('Microsoft Office 2003', 'Office 2003'),
('Microsoft 2003 Office', 'Office 2003'),
('Microsoft Office 2003 Professional', 'Office 2003'),
('Adobe', 'Adobe Inc')
GO
SELECT * FROM BigTable as Source_TABLE
SELECT * FROM RefTable as Lookup_TABLE
GO

insert into RefTable(software_name_raw,software_name_amended)
select
bt.software_name_raw,
'Needs Updating'
from
BigTable bt
where
not exists(select 1 from RefTable rf where rf.software_name_raw = bt.software_name_raw);

select * from RefTable;