Update statement with 'MAX' - 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 2000 Forums

Transact-SQL (2000)

Update statement with 'MAX'

Author

Topic

mmartins
Starting Member

2 Posts

Posted - 2010-06-09 : 15:19:49

Hi,

I have the following SELECT statement;


select max(lastdate), fk_id from log  where type = ‘X’ AND (fk_id IN
                          (SELECT DISTINCT id
                            FROM          log
                            WHERE      type = ‘A’))
			AND (fk_id IN
                          (SELECT DISTINCT id
                            FROM          log
                            WHERE      type = ‘C’))
group by fk_id

It selects the most recent log record for a set of users that match the WHERE clause.

I am trying to write an update statement that will update the most recent record of type X for a unique user and change it to type B. I am not sure how to write the update statement that will target only the most recent entry.

The code will only run once to fix some bad data that was entered into the database by the web application.

I tried to do my homework and do a search for the answer before posting my question but I couldn't find an answer for it.

Thank you,
Marcus

Kristen
Test

22859 Posts

Posted - 2010-06-09 : 16:04:58

I think I'd do it this way:


UPDATE U
SET type = 'B'
FROM log AS U
     JOIN
(
        SELECT TOP 1 id
        FROM log
        WHERE     type = 'X'
              AND ...
        ORDER BY lastdate DESC
) AS X
    ON X.id = U.id

mmartins
Starting Member

2 Posts

Posted - 2010-06-09 : 18:16:47

I think it will work for me. I am testing it right now.

Thanks!

Marcus

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources