updating all rows but one - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

updating all rows but one

Author

Topic

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-11-18 : 12:45:18

Please help!

tblLogComments - table

ProjectID Comments CurrentDate Submitted (fields)
---------------------------------------------------------
1234 testing 11/20/2008 false
1234 testing 10/11/2008 false
1234 testing 11/09/2008 false
1234 testing 06/15/2008 false
1234 testing 11/14/2008 false
3235 testing 11/11/2008 false
3235 testing 11/15/2008 false
3235 testing 11/01/2008 false
3235 testing 10/23/2008 false
3235 testing 09/14/2008 false

Above is my table and the fields in it. I want to update the submitted field (this is a boolean field) to true for all the records but one. I don't want to update the record that has the latest date. In the example above, I want to update all the rows for projectID 1234 except for the row that has the date 11/20/2008 (last recorded date).

How do I do this in SQL?

Thanks

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-11-18 : 13:00:14

more info...

I can't do "WHERE CurrentDate <> 11/20/2008" because I have more than 10,000+ records in this table and there are more than 200+ projectID's and I need to this update for all the projects in the table. The sql should determine tha last entered comments based on the current date and exclude that record in the update process.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-18 : 13:06:38

UPDATE TblLogComments
SET submitted = 1
WHERE currentdate IN (SELECT top(1) currentDate FROM tblLogComments ORDER BY currentDate Desc)
AND projectId = 1234

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 13:36:08

[code]
UPDATE t
SET t.submitted = 'True'
FROM TblLogComments t
INNER JOIN (SELECT ProjectID,MAX(CurrentDate)AS latest
FROM TblLogComments
GROUP BY ProjectID)t1
ON t1.ProjectID=t.ProjectID
AND t1.latest=t.CurrentDate
[/code]

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-11-18 : 13:36:49

This doesn't work the way I want it. The sql should go thro' all the rows and do the update for each of the projectID's meaning I should be able to update all the rows expect for the one row that has the latest date, for each of the projectsID's (1234, 3235, so forth)

I'm trying to avoid doing "WHERE ProjectID = 1234", "WHERE ProjectID = 3235" and so forth.

To summarize it all,

For each ProjectID, update all the rows with submitted set to true except for the row that has the latest current date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 13:40:29

quote:
Originally posted by rum23

This doesn't work the way I want it. The sql should go thro' all the rows and do the update for each of the projectID's meaning I should be able to update all the rows expect for the one row that has the latest date, for each of the projectsID's (1234, 3235, so forth)

I'm trying to avoid doing "WHERE ProjectID = 1234", "WHERE ProjectID = 3235" and so forth.

To summarize it all,

For each ProjectID, update all the rows with submitted set to true except for the row that has the latest current date.

try my posted suggestion

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-11-18 : 13:49:17

solution posted by visakh16 worked flawlessly!

Thanks a ton.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 13:54:00