I'm working on a SQL query that needs to go and update a date in one table, with values from another table, if the values in the first table match a specified list. I wrote a Query already that can go through the entire table and update the dates, but now I've been given a select list that a customer wants updated, instead of the entire table. In .Net, I'd make an array and store the values in there and loop through that, but not sure if what I need in SQL is a WHILE loop, or if what I have below will work good enough?
I need to basically update values in the database When one of the values matches the "IN" clause I have...so for example, if you find Value 1, take it's date and put it in the matching date in another table for that UID, take the date for UID #2, do the same thing, etc.
Thanks in Advance:
/* SQL Script to go through UIDDetail table and take Accepted dates and fill in for */
/* Corresponding field in UIDMark Table for Mark Effective Date */
/* Version 0.1 by Evan Richardson - GSS Oct-29-2010 */
/* Tested so far against IGuides 3.5 SQL DB only!!! */
/* This is for General Dynamics IT IGuides only!!! */
/*Declare the variables we'll use for placeholders */
DECLARE @AcceptDate datetime
DECLARE @AcceptID INT
SET @AcceptID = (
SELECT [UIDDetail_ID]
FROM [IGUIDES].[dbo].[UIDDetail]
WHERE [UIDNbr] IN('D1R7S3PS7500-L100M-XA175-10013111', 'D1R7S3PS7500-L100M-XA175-10013159', 'D1R7S3PS7500-L100M-XA175-10013300', 'D1R7S3PS7500-L100M-XA175-10013451', 'D59951KVT417A-1UV-A09040057', 'D6703202-2799081-1BDB42C1', 'D6703202-2799237-1000013', 'D6703203-2802005-1FTX1329A0QM'))
/* fill the Accepted Date and ID Variables */
SET @AcceptDate = (SELECT [AcceptDate] FROM [IGUIDES].[dbo].[UIDDetail] WHERE [UIDDetail_ID]=UIDNbr)
SET @AcceptID = (SELECT [UIDDetail_ID] FROM [IGUIDES].[dbo].[UIDDetail] WHERE [UIDDetail_ID]=UIDNbr)
/* Begin Updating the Effective Date in the UIDMark Table with the
Value taken from the UIDDetail Table, using the UIDDetail_ID value
as the control, since it's the linking value between the two tables */
IF @AcceptDate IS NOT NULL
UPDATE [IGUIDES].[dbo].[UIDMark]
SET [EffectiveDate] = @AcceptDate
WHERE [UIDDetail_ID] = @AcceptID /* <--- That is the value we declared above */
EDIT:
I think I might have figured it out, by declaring a table variable:
/* SQL Script to go through UIDDetail table and take Accepted dates and fill in for */
/* Corresponding field in UIDMark Table for Mark Effective Date */
/* Version 0.2 by Evan Richardson - GSS Oct-29-2010 */
/* Tested so far against IGuides 3.5 SQL DB only!!! */
/* This is for General Dynamics IT IGuides only!!! */
/*Declare the variables we'll use for placeholders */
DECLARE @AcceptDate datetime
DECLARE @AcceptID INT
DECLARE @UIDsToModify TABLE (UIDDetail_ID INT, AcceptDate datetime)
DECLARE @counter INT
DECLARE @FirstRow INT
DECLARE @EndRow INT
INSERT INTO @UIDsToModify
SELECT [UIDDetail_ID], [AcceptDate]
FROM [IGUIDES].[dbo].[UIDDetail]
WHERE [UIDNbr] IN( 'D1R7S3PS7500-L100M-XA175-10013111',
'D1R7S3PS7500-L100M-XA175-10013159',
'D1R7S3PS7500-L100M-XA175-10013300',
'D1R7S3PS7500-L100M-XA175-10013451',
'D59951KVT417A-1UV-A09040057',
'D6703202-2799081-1BDB42C1',
'D6703202-2799237-1000013',
'D6703203-2802005-1FTX1329A0QM')
SET @FirstRow = (SELECT MIN(UIDDetail_ID) FROM @UIDsToModify)
SET @EndRow = (SELECT MAX(UIDDetail_ID) FROM @UIDsToModify)
SET @counter = @FirstRow-1
/* Begin Looping through the Counter variable for each row in the table until we hit the end */
WHILE @counter <= @EndRow
BEGIN
/* Start incrementing the counter */
SET @counter = @counter+1
/* fill the Accepted Date and ID Variables */
SET @AcceptDate = (SELECT [AcceptDate] FROM @UIDsToModify WHERE [UIDDetail_ID]=@counter)
SET @AcceptID = (SELECT [UIDDetail_ID] FROM @UIDsToModify WHERE [UIDDetail_ID]=@counter)
/* Begin Updating the Effective Date in the UIDMark Table with the
Value taken from the UIDDetail Table, using the UIDDetail_ID value
as the control, since it's the linking value between the two tables */
IF @AcceptDate IS NOT NULL
UPDATE [IGUIDES].[dbo].[UIDMark]
SET [EffectiveDate] = @AcceptDate
WHERE [UIDDetail_ID] = @AcceptID /* <--- That is the value we declared above */
END
Sorry, MS, don't care. Just sold my XSX and upgraded to the PS5 Pro.
ALL the multi-platform games are available on the PS5, but what the Xbox doesn't have:
- VR games
- PS exclusives like TLOU
Xbox exclusives? Never mind, you just released Forza and Gears on the PS5. Halo? Oh I can play that on the PC with Xbox controllers. If I mod it, I can even play in split screen co-op.
So I'm really sorry, the platform is really good, I get free cloud saves without any sub, but.... the PS5 just gives me more games. TLOU, Horizon, and VR games. It really is all about exclusives. You can be the good guy, but that won't lead to success in this game.
Question
SirEvan
I'm working on a SQL query that needs to go and update a date in one table, with values from another table, if the values in the first table match a specified list. I wrote a Query already that can go through the entire table and update the dates, but now I've been given a select list that a customer wants updated, instead of the entire table. In .Net, I'd make an array and store the values in there and loop through that, but not sure if what I need in SQL is a WHILE loop, or if what I have below will work good enough?
I need to basically update values in the database When one of the values matches the "IN" clause I have...so for example, if you find Value 1, take it's date and put it in the matching date in another table for that UID, take the date for UID #2, do the same thing, etc.
Thanks in Advance:
EDIT:
I think I might have figured it out, by declaring a table variable:
Link to comment
https://www.neowin.net/forum/topic/949544-sql-how-to-loop-through-a-list-of-values/Share on other sites
10 answers to this question
Recommended Posts