[SQL] How to loop through a list of values?

[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:

/* 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         

[NateB1]

Couldn't you use an EXISTS clause in your update statement and eliminate the loop entirely?

The time of execution for your loop will increase linearly as the data increases, so I would *highly* recommend not using loops in SQL if you intend this query to be used on a regular basis. SQL is based on set theory, and performs most efficiently in that manner.

[jamieakers]

I'll second that, working singleton is very expensive compared to set based processing. That's how SQL was written I'm afraid.

Can't you do a joined update for what you're looking to achieve?

update uidmark
set uidmark.effectivedate = uiddetail.acceptdate
from uidmark
join uiddetail on uiddetail.uiddetail_id = uidmark.uiddetail_id
where uidmark.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')

If I'm missing something holler.

[Mattytommo]

Yeah like Nate said, you don't even need that loop. The table you have created should store all the information you need, but you need to update that table variable to store the date for each of those fields in it and update the select statement at the top to bring the dates back for those fields. You just need to do something like:

DECLARE @UIDsToModify TABLE (UIDNumber INT, EffectiveDate DATETIME)

INSERT INTO @UIDsToModify
SELECT [UIDDetail_ID], [EffectiveDate]
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')

UPDATE [IGUIDES].[dbo].[UIDMark]
SET [EffectiveDate] = @UIDsToModify.EffectiveDate
WHERE [UIDDetail_ID] = @UIDsToModify.UIDDetail_ID

Note I haven't compiled that (I don't have SQL at home I may have made a typo, but it gives you the idea anyway)

:)

  On 29/10/2010 at 21:29, jamieakers said:

I'll second that, working singleton is very expensive compared to set based processing. That's how SQL was written I'm afraid.

Can't you do a joined update for what you're looking to achieve?

update uidmark
set uidmark.effectivedate = uiddetail.acceptdate
from uidmark
join uiddetail on uiddetail.uiddetail_id = uidmark.uiddetail_id
where uidmark.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')

If I'm missing something holler.

You could use a join yeah, it would be an Inner Join though I believe? I don't think that code will work, I could be wrong but I don't think Update statements can be formed like UPDATE -> SET -> FROM, needs to be UPDATE -> SET -> WHERE.

:)

[still1]

Join update is the best way to do this. Also you can provide a where condition in the update to update only the fields you need.

you can find some examples here.

http://geekswithblogs.net/faizanahmad/archive/2009/01/05/join-in-sql-update--statement.aspx

[jamieakers]

  On 29/10/2010 at 21:57, Mattytommo said:

Yeah like Nate said, you don't even need that loop. The table you have created should store all the information you need, but you need to update that table variable to store the date for each of those fields in it and update the select statement at the top to bring the dates back for those fields. You just need to do something like:

DECLARE @UIDsToModify TABLE (UIDNumber INT, EffectiveDate DATETIME)

INSERT INTO @UIDsToModify
SELECT [UIDDetail_ID], [EffectiveDate]
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')

UPDATE [IGUIDES].[dbo].[UIDMark]
SET [EffectiveDate] = @UIDsToModify.EffectiveDate
WHERE [UIDDetail_ID] = @UIDsToModify.UIDDetail_ID

Note I haven't compiled that (I don't have SQL at home I may have made a typo, but it gives you the idea anyway)

:)

You could use a join yeah, it would be an Inner Join though I believe? I don't think that code will work, I could be wrong but I don't think Update statements can be formed like UPDATE -> SET -> FROM, needs to be UPDATE -> SET -> WHERE.

:)

Yeah I'm a little rusty... !

Was a little baffled as to the need for in memory tables; thanks for your comments as I was almost convinced I was missing something.

[Mattytommo]

  On 29/10/2010 at 22:02, still1 said:

Join update is the best way to do this. Also you can provide a where condition in the update to update only the fields you need.

you can find some examples here.

http://geekswithblogs.net/faizanahmad/archive/2009/01/05/join-in-sql-update--statement.aspx

I disagree that it's the best way to do it, probably near enough the same in both execution and complexity. Joins vs Subquerys is quite the debated topic, but it really is down to personal preference as there isn't much difference between the two. :)

  On 29/10/2010 at 22:06, jamieakers said:

Yeah I'm a little rusty... !

Was a little baffled as to the need for in memory tables; thanks for your comments as I was almost convinced I was missing something.

Hehe, well a better way to do that would be to create a temporary table, like the following:

CREATE TABLE #temptable

This way the temporary table only stays in memory for the lifetime of the query. (Note: the hash means temporary)

[Kami-]

Never iterate for updates... use SET based queries... -.-

Iterations should usually only be used for 'running totals'..

[BGM]

you don't loop in SQL, you used set based operations..

99% of problems can be solved without looping / cursors

[Kami-]

  On 01/11/2010 at 19:56, BGM said:

you don't loop in SQL, you used set based operations..

99% of problems can be solved without looping / cursors

:p

[Mattytommo]

  On 01/11/2010 at 22:10, Anon... said:

:p

To be fair you were both late to the party, so

:p :p