CODEWITHSUNDEEP
sqlsql-server-2005
SysDragon
SysDragon

Reputation: 9888

Delete data with subselect and multiple primary key fields and conditions

I have this data:

MyDate      id     hour   Value   insertionTime           id_agent
----------  -----  -----  ------  ----------------------  --------
2013-12-18  14     17     63      2013-12-18 11:35:47     10
2013-12-18  14     18     63      2013-12-18 11:35:47     10
2013-12-18  14     18     13      2014-01-14 11:02:27     10
2013-12-18  14     19     63      2013-12-18 11:35:47     10
2013-12-18  127    20     63      2013-12-18 11:35:47     10
...

I need to delete all the columns that have an insertionTime different than 2014-01-14 11:02:27 and have another row for the same MyDate, id and hour.

I don't know how to do it on a single DELETE execution. I cannot use an aggregate function and since there is multiple keys on the PK I don't know how to make a subselect.

Ideally, it would be something like:

DELETE FROM Table1 WHERE MyDate = @ADate AND id_agent = 10
AND insertionTime <> '2014-01-14 11:02:27' AND AllMyTableKeys IN
 (SELECT AllTableKeys FROM Table1 WHERE MyDate = @ADate  AND id_agent = 10
  GROUP BY AllTableKeys HAVING COUNT(*) > 1)

In the example I want to delete the second row.

Upvotes: 1

Views: 124

Answers (1)

SysDragon
SysDragon

Reputation: 9888

Made to the solution using EXISTS:

SELECT * FROM MyTable a WHERE MyDate = '12/18/2013' AND id_agent = 10
AND insertionTime <> '01/14/2014 11:02:27' AND EXISTS 
  (SELECT * FROM MyTale b WHERE a.MyDate = b.MyDate AND a.hour = b.hour
   AND a.id = b.id AND insertionTime = '01/14/2014 11:02:27' AND id_agent = 10)

To put that into a DELETE form is:

DELETE a FROM MyTable a WHERE...

Upvotes: 1

Related Questions