CODEWITHSUNDEEP
sqlsql-servert-sqlsql-delete
C B
C B

Reputation: 13314

SQL Server delete records

I have two tables computers, and softwareinstalls.

Computers primary key is computerid, softwareinstalls has three fields (installid, computerid, and lastmodifieddate).

Software installs computerid joined with computers.computerid.

I'm trying to delete all softwareinstalls on a computer that have a last modified date more than 1 day before the max for that computer, so...

software installs table

install  computerid  lastmodifieddate
1        1           01-16-13
2        1           01-16-13
3        1           01-14-13
4        2           01-12-13
5        2           01-10-13

would delete records 3 and 5. what would be the query in sql server?

Upvotes: 1

Views: 187

Answers (1)

John Woo
John Woo

Reputation: 263683

You can join on delete statements. Use LEFT JOIN on this.

DELETE  a
FROM    softwareinstalls a
        LEFT JOIN
        (
            SELECT computerID, max(lastmodifieddate) max_date
            FROM softwareinstalls
            GROUP BY computerID
        ) b ON a.computerID = b.computerID AND
                a.lastmodifieddate = b.max_date
WHERE   b.computerID IS NULL

For better performance, add an index on columns computerID and lastmodifieddate.

Upvotes: 1

Related Questions