October 30, 2009 at 12:22 pm
I am running a series of replace commands on a DB, for example:
UPDATE TABLE
SET FULLNAME = REPLACE(FULLNAME,'%TOMMY%','TOM')
I would like to track whenever the replace command actually changes something as opposed to skipping over the record. Is there a good way to do this?
October 30, 2009 at 12:24 pm
update dbo.MyTable
set Fullname = replace(Fullname, 'Tommy', 'Tom')
where Fullname like '%Tommy%';
select @@rowcount as RowsAffected;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 30, 2009 at 12:46 pm
Use an OUTPUT clause to return the rows that were updated, and add a WHERE clause to only update rows that should be changed:
DECLARE @Output_MyTable table
(
MyTablePKintprimary key clustered,
Before_FULLNAMEvarchar(50),
After_FULLNAMEvarchar(50)
)
UPDATE MyTable
SET
FULLNAME = REPLACE(FULLNAME,'%TOMMY%','TOM')
OUTPUT
INSERTED.MyTablePK,
DELETED.FULLNAME,
INSERTED.FULLNAME
INTO
@Output_MyTable
WHERE
FULLNAME <> REPLACE(FULLNAME,'%TOMMY%','TOM')
October 30, 2009 at 3:36 pm
Thank you for the answers.
Michael, I'm a bit confused on the code below, I'm not familiar with output statements. In particular, I'm not sure what this block of code does:
INSERTED.MyTablePK,
DELETED.FULLNAME,
INSERTED.FULLNAME
Michael Valentine Jones (10/30/2009)
Use an OUTPUT clause to return the rows that were updated, and add a WHERE clause to only update rows that should be changed:
DECLARE @Output_MyTable table
(
MyTablePKintprimary key clustered,
Before_FULLNAMEvarchar(50),
After_FULLNAMEvarchar(50)
)
UPDATE MyTable
SET
FULLNAME = REPLACE(FULLNAME,'%TOMMY%','TOM')
OUTPUT
INSERTED.MyTablePK,
DELETED.FULLNAME,
INSERTED.FULLNAME
INTO
@Output_MyTable
WHERE
FULLNAME <> REPLACE(FULLNAME,'%TOMMY%','TOM')
October 30, 2009 at 4:08 pm
'Deleted' and 'Inserted' table are created and maintained automatically by SQL Server. Both are temporary, memory-resident tables to test the effects of your data modifications
Inserted - contain new data (after update statement - in your case)
Deleted - old set of data (before update statement - in your case)
October 30, 2009 at 5:55 pm
huston.dunlap (10/30/2009)
...Michael, I'm a bit confused on the code below, I'm not familiar with output statements...
What did you find when you looked it up in SQL Server 2005 Books Online?
October 31, 2009 at 9:40 am
Michael Valentine Jones (10/30/2009)
huston.dunlap (10/30/2009)
...Michael, I'm a bit confused on the code below, I'm not familiar with output statements...What did you find when you looked it up in SQL Server 2005 Books Online?
I am actually still pretty new to SQL, so I'm not familiar with all the good reference materials. I did finally find an article on this site that talks about the output statement, so that and your explanations I should be good to go.
Thank you very much!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply