• 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

    • Microsoft locks Windows 11 user out, shows how easy losing data from forced encryption is by Sayan Sen Back in March earlier this year, a new redesigned Microsoft Account sign-in was released with the intention to make it "more modern, simple, and secure." Microsoft also probably hopes that the revamp will help win some hearts since many dislike the Microsoft Account (MSA) quite a bit as they are forced to use the service during Windows 11 installation. Yes, signing in to the MSA is one of the several system requirements for Windows 11, and it is also the recommended way and it clearly does not like it when users opt for a Local account instead. Microsoft often highlights the benefits of an MSA as it points out the unified access users get across devices and services like Windows, Office, OneDrive, and Xbox, which can help in synchronization of files and settings for convenience. A Microsoft Account also stores the BitLocker encryption key which is crucial thing that all users who have encryption need to store securely. Back in May this year, we covered reports of users losing their data as a consequence of BitLocker key loss, and this is a real danger for many, given that Microsoft now enables automatic BitLocker encryption on Windows 11 24H2, that most users won't even be aware of. So in the case of loss of access to a Microsoft Account, an affected user can suddenly find that they have lost all their data and there may be no way to recover it according to Microsoft's terms. Such account lock-outs can happen as a Reddit user deus03690 found out. The frustrated user claims that Microsoft apparently "randomly" locked their account when they were dealing with multiple data drives. They explain: The user has good reason to be annoyed and frustrated at this, Microsoft's own official guidance about the Account lock says: "If you tried to sign in to your account and received a message that it's been locked, it's because activity associated with your account might violate our Terms of Use." The Terms of Use for MSA explain how Microsoft deals with a closed account. It states: Thus, this shows how users can be pretty much helpless if they get locked out of MSA or lose access to it. It also shows how over-reliance on cloud services on Windows 11, something which LibreOffice recently pointed out, can lead to additional data nightmares like losing all of your data due to forced BitLocker encryption that you may not even be aware of was there in the first place. The solution? Better keep your important data backed up locally on internal or external HDDs and SSDs as only cloud storage is probably not the best decision.
    • I don't know, I haven't checked what changed in previous sockets. I agree that the 1156-1155-1151 succession was suspicious, with a reduction in pin count every time. Intel could do a better job of pre-allocating pins for future use. Another hypothesis is that the internal layout of their CPUs change, like the I/O is moved from one place to another on the chip, and they need to reorganize pins rather than having circuitry go into spaghetti mode to remain compatible. I agree that if AMD is able to maintain compatibility, Intel should be able to do the same, at least by reserving pins for future use and then using those pins when a need for them arises. However, I wouldn't say that AMD's products are entirely better. Intel's I/O now slightly edges out thanks to having double the bandwidth to the chipset and dedicated Thunderbolt lanes to the CPU. It seems that they could widen their lead with the next platform. NVMe SSDs have increased the need for PCIe lanes significantly, and AM5 has been pretty underwhelming in that regard, especially because the chipset connection is so narrow and gets saturated with just 1 gen 4 SSD, leaving the other chipset connectivity (Ethernet, Wi-Fi, audio, etc) to hope for any remaining bandwidth. Otherwise motherboard manufacturers could also make more x2 M.2 slots, those would be fast enough at gen 5 speeds and possibly at gen 4 speeds too.
  • Recent Achievements

    • Week One Done
      korostelev earned a badge
      Week One Done
    • 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
    • Enthusiast
      Motoman26 went up a rank
      Enthusiast
  • Popular Contributors

    1. 1
      +primortal
      675
    2. 2
      ATLien_0
      264
    3. 3
      Michael Scrip
      184
    4. 4
      +FloatingFatMan
      177
    5. 5
      Steven P.
      140
  • Tell a friend

    Love Neowin? Tell a friend!