How to do something for each result in a data set

  • select distinct(employeeid) from dbo.rollingdutyrostershift where rollingdutyrosterid = 8)

    selects 13 different employeeid's (37, 74, 75 etc)

    For each, I want to make this update:

    Update dbo.rollingdutyrostershift set employeegroupid = (select employeegroupid from dbo.employeegroupmaptoemployee where employeeid = 75) where employeeid = 75

    where '75' is the employeeid's from the first select statement (i.e. 37,74,75 etc)

    How to?

    Edvard Korsbæk

  • UPDATE r

    SET employeegroupid = tmp.employeegroupid

    FROM

    dbo.rollingdutyrostershift r

    INNER JOIN

    (

    SELECT DISTINCT employeeid, employeegroupid

    FROM dbo.rollingdutyrostershift

    WHERE rollingdutyrosterid = 8

    ) tmp

    ON r.employeeid = tmp.employeeid

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • First - This question was misplaced.

    Its repeated in a better place (Forum), where i got a proper answer.

    But, I really want to be able to run through a data set if i want to.

    Even if its not the best way - Now and again, Its better to be fast, and I (And the rst of the gang at my´office) was not able to make the correct update query.

    But if I had been able to run through the result set, i had delivered an answer before weekend.

    Best regards

    Edvard

  • edvard 19773 (4/17/2012)


    First - This question was misplaced.

    Its repeated in a better place (Forum), where i got a proper answer.

    But, I really want to be able to run through a data set if i want to.

    Even if its not the best way - Now and again, Its better to be fast, and I (And the rst of the gang at my´office) was not able to make the correct update query.

    But if I had been able to run through the result set, i had delivered an answer before weekend.

    Best regards

    Edvard

    If you really want to loop through the dataset (which has horrible performance of course), you can issue your select statement in an Execute SQL Task. Configure it to store the full result set in an object variable. Create a for each loop, use an ADO enumerator and configure it to loop over the object variable. Map each column of the row to a variable.

    Inside the for each loop, put your update statement:

    Update dbo.rollingdutyrostershift set employeegroupid = (select employeegroupid from dbo.employeegroupmaptoemployee where employeeid = ?) where employeeid = ?

    In the parameter mappings tab of the Execute SQL Task, map your variable that is populated in the for each loop twice (once for each question mark, which is a parameter placeholder).

    Another option is to put your select statement in an OLE DB source and use an OLE DB Command to do the update. It has the same SQL statement (with the question marks).

    Again, horrible performance.

    An TSQL update statement like this one - using a subquery - shouldn't be so hard to write. There are examples in the MSDN documention of the UPDATE statement. And you should be able to find plenty of resources on the net that explain you how to write such an update statement.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply