• 0

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


Question

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         

10 answers to this question

Recommended Posts

  • 0

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.

  • 0

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.

  • 0

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.

:)

  • 0

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

  • 0
  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.

  • 0
  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)

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Sorry, no - Data protection is solely the responsibility of the user. If you value the data, you have multiple copies.
    • Is this phone actually made in the USA?
    • Watchdog finds Microsoft guilty of confusing advertising when it comes to Copilot by Usama Jawad While Microsoft is advocating for Copilot usage in both personal and enterprise environments as much as possible, it arguably hasn't made as big of a splash as it wanted. Now, the U.S. watchdog National Advertising Division (NAD) has criticized Microsoft for some of its advertising practices when it comes to Copilot. In a recent blog post spotted by The Verge, NAD noted that Copilot does possess the functionalities that Microsoft expressly claims it does in enterprise use cases. This includes capabilities like summarization of content, generating text, and creating outlines for PowerPoint slide decks. While Copilot does have some limitations in this area, NAD thinks that they are pretty minor, so the lack of disclosure in this area doesn't really impact how most customers leverage Copilot. That said, the watchdog discovered some problems in Microsoft's messaging when it came to Business Chat. It believes that Redmond's universal and interchangeable use of Copilot for both the assistant and Business Chat can cause confusion among users, as they judge their functionalities. For example, NAD argues that while Copilot is quite fast at seamlessly generating documents in other applications, this is not the case with Business Chat, which requires more manual steps, and this is not abundantly clear in the advertising on Microsoft's website. Finally, NAD advised Microsoft to get rid of its claims regarding an increase in productivity and ROI because while the tech giant does have supporting studies to back its boasts, they do not provide a "good fit" for objective claims. It's also worth highlighting that the UK government recently did trial Copilot in work environments and discovered that participants did save 26 minutes per day on average. Still, it seems like this is not good enough for NAD. Interestingly, Microsoft did respond to NAD's advisory, saying that while it disagrees with some of the watchdog's findings, it has made changes to its advertising messages in order to comply with NAD's recommendations. Source: NAD via The Verge
    • My new emergency poo ticket holder
    • These are the same ignorant, gullible, cowardly fools who still believe that gods are real and that fictional White Jesus is coming back to Earth "any day now". It's a Venn-diagram of two almost perfectly concentric circles. In short, they'll buy anything you lie to them about as long as it makes them less afraid... Of things the rest of us know we don't have to be afraid of at all.
  • Recent Achievements

    • Experienced
      dismuter went up a rank
      Experienced
    • One Month Later
      mevinyavin earned a badge
      One Month Later
    • Week One Done
      rozermack875 earned a badge
      Week One Done
    • Week One Done
      oneworldtechnologies earned a badge
      Week One Done
    • Veteran
      matthiew went up a rank
      Veteran
  • Popular Contributors

    1. 1
      +primortal
      700
    2. 2
      ATLien_0
      268
    3. 3
      Michael Scrip
      213
    4. 4
      +FloatingFatMan
      186
    5. 5
      Steven P.
      143
  • Tell a friend

    Love Neowin? Tell a friend!