April 14, 2012 at 11:53 pm
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 15, 2012 at 12:58 am
Please check this query:
UPDATE t1
SET t1.employeegroupid = t2.employeegroupid
FROM dbo.rollingdutyrostershift t1
INNER JOIN dbo.employeegroupmaptoemployee t2
ON t1.employeeid = t2.employeeid
WHERE t1.employeeid IN (37, 74, 75 etc)
April 15, 2012 at 2:18 am
Did the trick - Thanks:-).
Not urgent, but how to make it in a WHILE loop instead.
I do not need it here, but it seems wrong, that i cannot do it as i program in all other languages.
Best regards
Edvard Korsbæk
April 15, 2012 at 2:44 am
edvard 19773 (4/15/2012)
Did the trick - Thanks:-).Not urgent, but how to make it in a WHILE loop instead.
I do not need it here, but it seems wrong, that i cannot do it as i program in all other languages.
Best regards
Edvard Korsbæk
You can do it, but I don't suggest you do, as it will be an order of magnitude slower and will require more lines of code.
SQL Server is optimised for set-based operations, not row-by-row operations.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 15, 2012 at 3:11 am
Still thanks for having solved my immideate problem!
I am aware of, that your solution is much faster than a row by row solution, and in this case, its the right way to do it.
But sometimes, its just much easyer to see 'How to' solve a problem that way
That was the case here - I could not figure out how to make your query, but in a program code, i would have used less than 5 minutes to write the loop.
So, i still want to be able to make a loop on the distinct employee's - Not for use now, but for use in the future, when i need to solve something, which is easier understndable that way.
Best regards
Edvard Korsbæk
April 15, 2012 at 4:26 am
edvard 19773 (4/15/2012)
Still thanks for having solved my immideate problem!I am aware of, that your solution is much faster than a row by row solution, and in this case, its the right way to do it.
But sometimes, its just much easyer to see 'How to' solve a problem that way
That was the case here - I could not figure out how to make your query, but in a program code, i would have used less than 5 minutes to write the loop.
So, i still want to be able to make a loop on the distinct employee's - Not for use now, but for use in the future, when i need to solve something, which is easier understndable that way.
Best regards
Edvard Korsbæk
I continue to advise against it. Instead, I recommend presenting your problems to the community here and learning how to do things the way a database developer would do them. Your solutions will be faster (in every sense) and you'll learn new skills along the way.
If a particular problem requires a row-by-row solution (there are some), one will no doubt be provided. WHILE loops are supported in T-SQL if required.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 15, 2012 at 8:03 am
Phil Parkin (4/15/2012)
edvard 19773 (4/15/2012)
Still thanks for having solved my immideate problem!I am aware of, that your solution is much faster than a row by row solution, and in this case, its the right way to do it.
But sometimes, its just much easyer to see 'How to' solve a problem that way
That was the case here - I could not figure out how to make your query, but in a program code, i would have used less than 5 minutes to write the loop.
So, i still want to be able to make a loop on the distinct employee's - Not for use now, but for use in the future, when i need to solve something, which is easier understndable that way.
Best regards
Edvard Korsbæk
I continue to advise against it. Instead, I recommend presenting your problems to the community here and learning how to do things the way a database developer would do them. Your solutions will be faster (in every sense) and you'll learn new skills along the way.
If a particular problem requires a row-by-row solution (there are some), one will no doubt be provided. WHILE loops are supported in T-SQL if required.
i only use a while loop if im using the product of the @Count directly in what ever im doing. otherwise there is usually a set based method for doing any thing in SQL.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply