Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
obiwaugh
Starting Member
27 Posts |
 Posted - 2005-07-08 : 12:34:54
|
| What's wrong with this?If I remove the group by clause, it executes...but incorrectly. I need the group by clause to find the higest file number for each paygroup in PS_JOB....and then have that number be set as the last_file_number for the matching paygroup in PS_PAYGROUP_TBL.UPDATE PS_PAYGROUP_TBLSET LAST_FILE_NUMBER = (SELECT MAX(J.FILE_NBR) FROM PS_JOB J WHERE PAYGROUP = J.PAYGROUP GROUP BY J.PAYGROUP)PS_PAYGROUP_TBL keeps track of the last file number assigned to all of the employees. PS_JOB is a job history table that stores, among other things, the employee's file number. I want the update statement to set look at all the employees in each paygroup, find the highest file number, and then set LAST_FILE_NUMBER to be that number.There are 10 different paygroups in PS_PAYGROUP_TBL. The subquery, executed by itself, returns the following (the highest file numbers from PS_JOB):101030 002075 005048 005020 007075 003111 100005 006111 008017 004042--------------------------------I know enough to know that I don't know enough. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-07-08 : 13:04:19
|
Either of these should work:(you needed to use your table alias "j" on the main table and refer to it in your correlated subquery)UPDATE j set LAST_FILE_NUMBER = ( SELECT MAX(FILE_NBR) FROM PS_JOB WHERE PAYGROUP = J.PAYGROUP )from PS_PAYGROUP_TBL j--OR thisUPDATE j set LAST_FILE_NUMBER = ( SELECT top 1 FILE_NBR FROM PS_JOB WHERE PAYGROUP = J.PAYGROUP order by File_Nbr desc )from PS_PAYGROUP_TBL j Be One with the OptimizerTG |
 |
|
|
|
|
|
UPDATE with grouped subquery