April 13, 2012 at 4:48 am
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
April 17, 2012 at 1:33 am
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
April 17, 2012 at 1:39 am
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
April 17, 2012 at 1:45 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy