CODEWITHSUNDEEP
sqlitegroup-by
Tobia Zambon
Tobia Zambon

Reputation: 7629

select where a couple of values are in subselect

I have a table with a primary key consisting of three fields:

IdPro, IdPri, IsComp

Actually I have some malformed situations where I have for example:

   IDPro IDPri  IsComp
    1      1     null
    1      1      0

I want to delete the rows where IsComp = null but ONLY if there is a correspondent doubled row.

I need to know if there is something like:

Delete from mytable where IsComp is null 
       AND Tuple(IDpro, IDPri) in 
           (SELECT Tuple(IDPro, IDPri) 
            FROM mytable group by IDPro, IDPri 
            HAVING Count(*) > 1 
           )

Thank you.

Upvotes: 0

Views: 95

Answers (1)

Salaros
Salaros

Reputation: 1452

You could try to create a fake unique key for your table by concatenating IDpro and IDPri: 

DELETE FROM mytable 
WHERE IsComp IS null AND 
      IDpro || ';' || IDPri IN (
         SELECT IDpro || ';' || IDPri 
         FROM mytable
         GROUP BY IDpro , IDPri 
         HAVING COUNT(*) > 1
      )

Upvotes: 1

Related Questions