Because I have both keys in the table - new and original. E.g.
DECLARE @new INT = 20
, @original INT = 10;
DECLARE @table TABLE
(
ID INT PRIMARY KEY,
Payload INT
);
INSERT INTO @table
VALUES ( 10, 10 ), (20, 15);
UPDATE @table
SET ID = @new
WHERE ID = @original;
SELECT *
FROM @table;
In any case, I figured the solution already. Here is my current procedure although I still need some fixes:
while @loop <=@tablesCount
begin
select @table_name = table_name from @TablesToProcess where id = @loop;
if upper(@table_name) not IN ('GUESTS','ACCESS','ADDRESS','ADDLINK','DAILY_SALE_HDR','DAILY_TRANSACT')
begin
set @sql = @sql + 'RAISERROR (''Processing table ' + quotename(@table_name) + ' iteration #' + cast(@loop as varchar(10)) + ' out of ' +
cast(@tablesCount as varchar(10)) + '..'' , 0, 1) with nowait;
'
IF not exists(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME = CU.TABLE_NAME
AND TC.TABLE_SCHEMA = CU.TABLE_SCHEMA
AND TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY' AND TC.TABLE_NAME = @table_Name
AND CU.COLUMN_NAME = 'guest_no') -- exclude cases where guest_no is a PK for the table as this is more complex case
begin
if exists (select 1 from INFORMATION_SCHEMA.COLUMNS c WHERE c.Table_Name = @table_name and c.COLUMN_NAME = 'guest_no')
SET @sql = @sql + ' UPDATE dbo.' + quotename(@table_name) + ' set guest_no = @newGuestNo ' +
case upper(@table_name) WHEN 'GST_PASS' then ', mod_sp = @salespoint, mod_op = @operator'
WHEN 'GST_ACTV' then ', gfwdstatus = 1'
else '' end +
'
where guest_no = @originalGuestNo;
'
if exists (select 1 from INFORMATION_SCHEMA.COLUMNS c WHERE c.Table_Name = @table_name and c.COLUMN_NAME = 'parent_no')
SET @sql = @sql + ' UPDATE dbo.' + quotename(@table_name) + ' set parent_no = @newGuestNo where parent_no = @originalGuestNo;
'
if upper(@table_name) IN ('TR_INFO', 'TRS_INFO')
set @sql = @sql + ' UPDATE dbo.' + quotename(@table_name) + ' set info_num = CAST(@newGuestNo as CHAR(17))
where info_num = CAST(@originalGuestNo as CHAR(17)) AND info_type = 10
;'
end
else -- primary key, more complex case
begin
select @updateCmd = 'WITH cte AS (select * FROM dbo.' + QUOTENAME(@table_name) + ' WHERE guest_no = @originalGuestNo)
UPDATE t
SET ' + stuff((select ', ' + quotename(c.COLUMN_NAME) + ' = cte.' + quotename(c.COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = @table_name
AND c.COLUMN_NAME NOT IN ('guest_no', 'last_mod') FOR XML PATH('')), 1, 2, '') + '
FROM dbo.' + QUOTENAME(@table_name) + ' t, cte
WHERE t.guest_no = @newGuestNo;
'
print @updateCmd;
set @sql = @sql + '
IF EXISTS (SELECT 1 FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @originalGuestNo)
BEGIN
IF NOT EXISTS (SELECT 1 FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @newGuestNo) -- simple case, only duplicate guest exists
UPDATE dbo.' + quotename(@table_name) + ' SET guest_no = @newGuestNo
WHERE guest_no = @originalGuestNo;
ELSE -- both guests exist in the table
IF (SELECT ISNULL(date_time, ''19000101'') FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @newGuestNo) >=
(SELECT ISNULL(date_time, ''19000101'') FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @originalGuestNo)
-- New guest is newer - just delete the duplicate
DELETE FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @originalGuestNo;
ELSE -- duplicate is newer
' + @updateCmd +
'
DELETE FROM dbo.' + quotename(@table_name) + ' WHERE guest_no = @originalGuestNo;
END
'
end
end
set @loop = @loop + 1;
end
set @sql = 'BEGIN TRANSACTION
' + @sql + '
COMMIT TRANSACTION'
if @debug = 1
select @SQL as SQL;
--execute sp_executeSQL @SQL, N'@newGuestNo decimal(17,0), @originalGuestNo decimal(17,0), @salespoint CHAR(6), @operator CHAR(6)',
-- @newGuestNo = @nNewGuest, @originalGuestNo = @nOriginalGuest, @salespoint = @salespoint, @operator = @operator;
end
go
execute dbo.siriussp_DedupeGuest @nOriginalGuest = 90000001, @nNewGuest = 91000001, @salespoint = 'RENTAL', @operator = 'ADMIN';
In other words, I just generate the UPDATE command for this case dynamically and then delete the original.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
My TechNet articles
Generating delete and update dynamically
