Duplicate All single rows in database table

Question

I have this table with multiple columns. Primary key is (type,ref,code) with row type t1, t2, and two states Valid (V) and Invalid (I).

Every invalid row in state t1 should have a valid row in the state t2, but my table already has some rows with Invalid state that don't have the valid row in the state t2.

Example:

   type    |  ref   |  code   | state .....
----------------------------------------
   t1      |  1     | c1      | V
   t1      |  2     | c1      | V
   t1      |  3     | c1      | I
   t2      |  3     | c1      | V
   t1      |  4     | c1      | V
   t1      |  5     | c1      | I

so i need to duplicate the missing rows

I'm using

INSERT INTO table (type,ref,code,state)
SELECT 't2',ref,code,'V' FROM table
WHERE EXISTS (SELECT ref,code,count(*) from table GROUP BY ref,code HAVING count(*)=1)
AND state='I'

but I'm getting

 Violation of PRIMARY KEY

I tried with

WHERE NOT EXISTS (SELECT ref,code,count(*) from table GROUP BY ref,code HAVING count(*)>1)
AND state='I'

and nothing hapend. Any dea how to perform this??

Answer 1

You can use the following query to get to-be-duplicated rows:

SELECT type, ref, code, state
FROM mytable AS t1
WHERE state = 'I' AND type = 't1' AND 
      NOT EXISTS (SELECT 1
                  FROM mytable AS t2
                  WHERE t1.ref = t2.ref AND t1.code = t2.code AND
                        state = 'V' AND type = 't2')

So, the INSERT statement can look like this:

INSERT INTO mytable 
SELECT 't2', ref, code, 'V'
FROM mytable AS t1
WHERE state = 'I' AND type = 't1' AND 
      NOT EXISTS (SELECT 1
                  FROM mytable AS t2
                  WHERE t1.ref = t2.ref AND t1.code = t2.code AND
                        state = 'V' AND type = 't2')