• 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

    • With Sony basically owning EVO, this device isn't that big of a surprise. Though I'm sure the price tag will be, we'll see.
    • wtf are you talking about... kinda ironic of you saying it looks dumb because you clearly have problems with your eyes. I get that you misread FIGHT as FLIGHT but how can you look at the actual device and still think it has anything to do with planes... have you never seen an arcade cabinet?
    • I don't usually complain about articles here, but this is just ridiculous, you couldn't ask your precious AI to write something about tech instead? This literally has nothing to do with tech.
    • Microsoft's "Athena" AI: A blueprint for your own dev team's productivity boost by Paul Hill Last month, Microsoft made many developer-oriented announcements at its annual BUILD conference. One of the tools that it announced at the time was called Athena, an artificial intelligence agent that lives in Microsoft Teams and aims to speed up product development processes. Now, however, Microsoft has released the blueprint of Athena so anyone can begin implementing a similar helper for their work. Rather than just another chatbot, Athena is a deeply integrated agent accessible through Teams that helps to connect people, tools, and data throughout the product development process. Athena is smart enough to work out what needs to happen next and helps team members get it done without having to go to different applications - Athena controls it all from Teams and you just have to communicate in natural language. Athena can be embedded in several developer workflows including Teams, Azure DevOps, and GitHub. Perhaps the greatest thing about Athena is that it’s not a new product being sold by Microsoft, but rather a methodology and open-source template (Dex) that organizations or individuals can take to build their own AI agents, allowing for more customization. To get started with Athena, you'll want to set up the Dex agent. Microsoft has also published a breakout session video about Athena so you can take a deeper dive. Who it affects, and how The primary beneficiaries of Microsoft’s decision to release Athena open source are engineering teams everywhere who will be freed from boring, repetitive tasks such as pull request (PR) reviews, work item management, and security checks. This will let them get on with coding new features and innovating - something that Microsoft has been pushing hard for since the start of the latest AI revolution. For product managers and engineering leaders, Athena also looks set to be incredibly useful as they will get better real-time visibility into the status of projects, if a release is truly ready for launch, and to ensure the team is aligned. Organizations from small to large will be able to benefit from using Athena. Due to its open-source nature, Athena can be tailored to meet specific development processes. This could unlock faster delivery cycles and improve code quality across the board. Why it's happening Athena is already being used internally at Microsoft by over 2,000 of its engineers. The Redmond giant explained that this has led to “measurable gains” in speed, quality, and focus. Aside from faster review cycles for developers, Athena is also surfacing release-blocking bugs earlier, enabling the consistent completion of security and privacy workflows, and providing quicker health assessments so that teams can gauge the overall health of their software delivery. By handling all these more boring tasks, Athena can free up developers to build more features into their projects. It also has the potential to speed up delivery times so that end users can use the new software faster, and with potentially less bugs. We often hear the term “democratization” in tech, a process that makes technologies more accessible and affordable. According to this definition, Microsoft’s release of Athena delivers on democratizing AI for developers as its open source and allows people to integrate AI Into their workflows, without starting from scratch. The move also aligns with Microsoft's AI strategy, that is, putting it all throughout its products. Copilot is probably the most notorious for its omnipresence in essentially every Microsoft product including Windows and Edge. Unlike Copilot, developers get a bit more freedom with Athena, but it’s still tied up with Microsoft products, namely Teams and GitHub. Caveats and what to watch for While it’s great that Microsoft is making its Athena blueprint accessible, one issue is that developers may still find it a bit complex to implement as there are still specific customizations organizations will want to make. Additionally, this solution involves a more involved setup process as outlined in the GitHub README. Another thing organizations should be wary about is data privacy and security implications when it comes to integrating with sensitive internal systems. Organizations that are working on secretive projects probably wouldn’t want to use Athena as this could put sensitive code in the hands of third parties. It’s not only technical issues that need considering either, there is also the human element. Some people may have concerns about AI hallucinating or ethical concerns around job security that could hurt adoption. To this end, Microsoft has reaffirmed that Athena is supposed to assist teams only, not replace team members. While Athena can be extremely useful, as shown by the results internally at Microsoft, human oversight and judgment will still be vital. Complex decision and creative problem-solving in development are some areas where a human still needs to be involved. Source: Microsoft
  • Recent Achievements

    • First Post
      James courage Tabla earned a badge
      First Post
    • Reacting Well
      James courage Tabla earned a badge
      Reacting Well
    • Apprentice
      DarkShrunken went up a rank
      Apprentice
    • Dedicated
      CHUNWEI earned a badge
      Dedicated
    • Collaborator
      DarkShrunken earned a badge
      Collaborator
  • Popular Contributors

    1. 1
      +primortal
      383
    2. 2
      +FloatingFatMan
      176
    3. 3
      ATLien_0
      169
    4. 4
      snowy owl
      169
    5. 5
      Xenon
      133
  • Tell a friend

    Love Neowin? Tell a friend!