T-SQL Query Help
in [Programming SQL]
|
Prev: ADO Command parameterized queries
Next: Problem prefixing object with dbo. when using system stored procedures
From: Rubens on 15 Jul 2008 10:53 I have 2 rather important database updates that I need to make today, and I want to make sure I get the syntax, actually more importantly the update correct! It's query 2 I am more concerned about. Requirements 1. Update 1: Update CustomChar4 in the Outlet table to the value in tTempTable. 2. Update 2: Update CustomChar4 in the Outlet table to NULL for all OutletIDs that are NOT in tTempTable AND contain a value of '1', '2', '3', '4' or '5'. FYI, the CustomChar4 field is a varchar(50) field. Can someone please tell me if my queries below are correct? Thank-you! Rubens -- Update 1 begin tran update Outlet set LastModified = getdate(), Revision = o.Revision + 1, CustomChar4 = t.CustomChar4 --select distinct OutletID, CustomChar4 --into dbo.Outlet_Backup20081115_CustomChar4_Update1 from Outlet o inner join tTempTable t on o.OutletID = t.OutletID --rollback tran commit tran go -- Update 2 begin tran update Outlet set LastModified = getdate(), Revision = Revision + 1, CustomChar4 = NULL --select distinct OutletID, CustomChar4 --into dbo.Outlet_Backup20081115_CustomChar4_Update2 from Outlet where OutletID not in ( select OutletID from tTempTable ) and CustomChar4 in ( '1','2','3','4','5' ) --rollback tran commit tran go
From: TheSQLGuru on 15 Jul 2008 11:07 I recommend making the updates into selects and running those to verify 100% that you have the correct query. I do this myself all the time. :-) -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Rubens" <rubensrose(a)hotmail.com> wrote in message news:uxKOJqo5IHA.3480(a)TK2MSFTNGP03.phx.gbl... I have 2 rather important database updates that I need to make today, and I want to make sure I get the syntax, actually more importantly the update correct! It's query 2 I am more concerned about. Requirements 1. Update 1: Update CustomChar4 in the Outlet table to the value in tTempTable. 2. Update 2: Update CustomChar4 in the Outlet table to NULL for all OutletIDs that are NOT in tTempTable AND contain a value of '1', '2', '3', '4' or '5'. FYI, the CustomChar4 field is a varchar(50) field. Can someone please tell me if my queries below are correct? Thank-you! Rubens -- Update 1 begin tran update Outlet set LastModified = getdate(), Revision = o.Revision + 1, CustomChar4 = t.CustomChar4 --select distinct OutletID, CustomChar4 --into dbo.Outlet_Backup20081115_CustomChar4_Update1 from Outlet o inner join tTempTable t on o.OutletID = t.OutletID --rollback tran commit tran go -- Update 2 begin tran update Outlet set LastModified = getdate(), Revision = Revision + 1, CustomChar4 = NULL --select distinct OutletID, CustomChar4 --into dbo.Outlet_Backup20081115_CustomChar4_Update2 from Outlet where OutletID not in ( select OutletID from tTempTable ) and CustomChar4 in ( '1','2','3','4','5' ) --rollback tran commit tran go
From: Plamen Ratchev on 15 Jul 2008 11:12 The first one seems good, you can also write it as: UPDATE Outlet SET LastModified = CURRENT_TIMESTAMP, Revision = Revision + 1, CustomChar4 = (SELECT T.CustomChar4 FROM tTempTable AS T WHERE T.OutletID = Outlet.OutletID) WHERE EXISTS(SELECT * FROM tTempTable AS T WHERE T.OutletID = Outlet.OutletID); In the second query if you have NULL values in the column OutletID in table tTempTable you will not get correct results. You can write it as: UPDATE Outlet SET LastModified = CURRENT_TIMESTAMP, Revision = Revision + 1, CustomChar4 = NULL WHERE NOT EXISTS(SELECT * FROM tTempTable AS T WHERE T.OutletID = Outlet.OutletID) AND CustomChar4 IN ('1', '2', '3', '4', '5'); HTH, Plamen Ratchev http://www.SQLStudio.com
From: Rubens on 15 Jul 2008 11:23 Thank-you Kevin. Actually, that is why I had the SELECT's commented out because I plan on running that first to look at the records beforehand. I am just waiting on the temp table from the user requesting the update. Thanks for that reminder, I know its saved my bacon a few times! Rubens "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message news:BeCdnQ8xmJltIeHVnZ2dnUVZ_jmdnZ2d(a)earthlink.com... I recommend making the updates into selects and running those to verify 100% that you have the correct query. I do this myself all the time. :-) -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Rubens" <rubensrose(a)hotmail.com> wrote in message news:uxKOJqo5IHA.3480(a)TK2MSFTNGP03.phx.gbl... I have 2 rather important database updates that I need to make today, and I want to make sure I get the syntax, actually more importantly the update correct! It's query 2 I am more concerned about. Requirements 1. Update 1: Update CustomChar4 in the Outlet table to the value in tTempTable. 2. Update 2: Update CustomChar4 in the Outlet table to NULL for all OutletIDs that are NOT in tTempTable AND contain a value of '1', '2', '3', '4' or '5'. FYI, the CustomChar4 field is a varchar(50) field. Can someone please tell me if my queries below are correct? Thank-you! Rubens -- Update 1 begin tran update Outlet set LastModified = getdate(), Revision = o.Revision + 1, CustomChar4 = t.CustomChar4 --select distinct OutletID, CustomChar4 --into dbo.Outlet_Backup20081115_CustomChar4_Update1 from Outlet o inner join tTempTable t on o.OutletID = t.OutletID --rollback tran commit tran go -- Update 2 begin tran update Outlet set LastModified = getdate(), Revision = Revision + 1, CustomChar4 = NULL --select distinct OutletID, CustomChar4 --into dbo.Outlet_Backup20081115_CustomChar4_Update2 from Outlet where OutletID not in ( select OutletID from tTempTable ) and CustomChar4 in ( '1','2','3','4','5' ) --rollback tran commit tran go
From: Rubens on 15 Jul 2008 11:26 >In the second query if you have NULL values in the column OutletID in table >tTempTable you will not get correct results. You can write it as: > >UPDATE Outlet >SET LastModified = CURRENT_TIMESTAMP, > Revision = Revision + 1, > CustomChar4 = NULL >WHERE NOT EXISTS(SELECT * > FROM tTempTable AS T > WHERE T.OutletID = Outlet.OutletID) > AND CustomChar4 IN ('1', '2', '3', '4', '5'); Gotcha, so this query will ensure the right records are updated. Thanks for the tip! Rubens "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:E1DE31FC-BFB8-4AE6-9782-B1E910BC32E3(a)microsoft.com... > The first one seems good, you can also write it as: > > UPDATE Outlet > SET LastModified = CURRENT_TIMESTAMP, > Revision = Revision + 1, > CustomChar4 = (SELECT T.CustomChar4 > FROM tTempTable AS T > WHERE T.OutletID = Outlet.OutletID) > WHERE EXISTS(SELECT * > FROM tTempTable AS T > WHERE T.OutletID = Outlet.OutletID); > > In the second query if you have NULL values in the column OutletID in > table tTempTable you will not get correct results. You can write it as: > > UPDATE Outlet > SET LastModified = CURRENT_TIMESTAMP, > Revision = Revision + 1, > CustomChar4 = NULL > WHERE NOT EXISTS(SELECT * > FROM tTempTable AS T > WHERE T.OutletID = Outlet.OutletID) > AND CustomChar4 IN ('1', '2', '3', '4', '5'); > > HTH, > > Plamen Ratchev > http://www.SQLStudio.com >
|
Next
|
Last
Pages: 1 2 Prev: ADO Command parameterized queries Next: Problem prefixing object with dbo. when using system stored procedures |