• 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's new AI tools: What "Researcher" and "Analyst" mean for your work by Paul Hill Microsoft has announced the general availability of two new reasoning AI agents called Researcher and Analyst. Both were previously available for Microsoft 365 Copilot Frontier members, but now they’re available for all Microsoft 365 Copilot license holders. Researcher is capable of multi-step research by combining OpenAI’s deep research model with Microsoft 365 Copilot’s orchestration and deep search capabilities. The Analyst agent can think like a data scientist, giving you insights in minutes from raw data. Analyst is built on OpenAI’s o3-mini. Microsoft says it can run Python to tackle the most complex data queries and you can view the code it’s running to verify its work in real time. Who it affects, and how While Frontier members have had access to these agents since April, they’ve only just been announced for general availability. The Copilot in question is not Microsoft’s free Copilot either, but the Copilot that comes as part of Microsoft 365 and includes additional features. To access it, you will have to pay for a $30 per month paid yearly subscription. Existing customers should now have access to both of these agents. While there is certainly angst in the world about the influence of AI on our jobs, Microsoft still maintains that it’s an assistant tool. These two new agents look set to benefit professionals across a range of roles including researchers and strategists, data analysts and scientists, sales and marketing teams, and anyone who just wants to summarize or synthesize information fast. The Researcher agent is helpful for gathering insights, preparing for negotiations, and assessing impacts such as the impact of tariffs on businesses. Meanwhile the Analyst agent can be used to convert raw data into actionable insights, identifying customer behaviors, and visualizing trends. It’s not all good news, Microsoft does have some limitations in place to ensure reliability of its service for all customers. The Redmond giant explains that the pre-pinned agents can run up to 25 combined queries per month - so that’s not 25 queries per agent, it’s for both together, each month. Additionally, Researcher supports 37 languages, but Analyst only supports eight, with more coming soon. Why it's happening Agents have been all the rage since the end of 2024 when figures in big tech declared that 2025 would be the year of agentic AI. Agents are capable of multi-step work and bring us closer to the goal of artificial general intelligence (AGI). These agents that Microsoft has unveiled are possible now thanks to the development of OpenAI’s deep research model and o3-mini, which also reasons. Earlier this year, Microsoft declared that it wanted to empower employees everywhere with AI agents and the release of Researcher and Analyst goes a long way in doing this. They will be beneficial for employees in many different fields and have the potential to free up a lot of time for more beneficial work. Customers in the Frontier program, Microsoft said, found these new tools to be highly effective for complex analytical work. This is great for Microsoft financially because it shows clear demand for such tools, justifying AI’s upfront development costs. These agents also help Microsoft keep up against the competition, which is also aggressively pursuing agents. What to watch for Microsoft said that its Researcher agent is much more accurate than everything that came before, thanks to the time it spends thinking about its answer. However, AI does still possess the ability, just like humans, to make mistakes. Verifying the creations of these agents is still crucial when it comes to anything mission critical. The Analyst agent’s ability to let the user see the steps and which Python code it executes is very good for transparency and can help combat errors if things ever start to go wrong with the agent’s reasoning. This could help to build trust among customers who need to use the Analyst agent and could set Microsoft’s offering apart from the competition, giving it an edge. Another thing customers should be aware of is the prompt they use matters. Microsoft tries to guide customers along with sample prompts but to get the most from these tools, users will need to know how to create effective and precise prompts. The good thing is that these bots are spoken with natural language, so it’s just a matter of being articulate and precise when you give a prompt. It will certainly be interesting to see how agents like these continue to affect employees’ job security in the future. While AI can certainly be helpful, if it develops to a point where an employer can effectively hire AI for a low cost to do the same work, then it could lead to massive displacement, with not enough new jobs for people to move into. This point has recently been elucidated by Anthropic’s CEO Dario Amodei. Source: Microsoft
    • I'm wondering if they are doing this as a "backup" in case CISA ceases to exist. It almost did recently due to funding and it's future is shaky. CISA - https://www.cisa.gov/known-exploited-vulnerabilities-catalog Example "CVE-2023-39780" https://www.cve.org/CVERecord?id=CVE-2023-39780 ASUS RT-AX55 Routers OS Command Injection Vulnerability
    • Over regulation is bad. That's why the EU is behind the US. But, it's a good thing the EU stepped in, in this case.
  • Recent Achievements

    • One Year In
      WaynesWorld earned a badge
      One Year In
    • First Post
      chriskinney317 earned a badge
      First Post
    • Week One Done
      Nullun earned a badge
      Week One Done
    • First Post
      sultangris earned a badge
      First Post
    • Reacting Well
      sultangris earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      173
    2. 2
      ATLien_0
      125
    3. 3
      snowy owl
      123
    4. 4
      Xenon
      118
    5. 5
      +Edouard
      91
  • Tell a friend

    Love Neowin? Tell a friend!