CODEWITHSUNDEEP
sqlpostgresql
mmalmeida
mmalmeida

Reputation: 1057

Recursive Insert into clause in Postgresql

I want to perform a big insert statement on multiple lines, but recursion is making it hard to build the correct SQL statement. I believe an example will make it easier to explain. Consider the model:

car


|id|code|Model name     |
|1 |100 |Deluxe         |
|10|100 |Deluxe improved|
|2 |200 |Standard       |
|20|200 |Standard new   |

color

|id|Name|
|2 |Red |
|3 |Blue|

car_colors

|id|car_id|color_id|
|3 |1     |2       |
|4 |2     |2       |
|5 |2     |3       |

The deluxe car was added, and afterwards the "deluxe improved" model was inserted. It's a new version of the same car (same code). Unfortunately, John Doe forgot to update the car_colors table, so now you want to update that table by inserting the same colors for every same car code.

In the example considered, we'd like to add the tuple "Deluxe improved, red" (because Deluxe and deluxe improved have the same code and Deluxe is available in red) and the tuples "standard new, red" and "standard new, black" for the same reasons.

The PSEUDO-CODE (non-sql) should be something like: all_cars_and_colors = select * from car left outer join car_colors

for each(this_car:all_cars_and_colors){

    if(all_cars_and_colors.color_id does not exist){
        car_colors_to_copy = select * from car inner join car_colors where car.code=this_car.code

        for each(color_to_copy: car_colors_to_copy){
            insert into car_colors(id,car_id,color_id) VALUES (nextval('id_sequence') ,this_car.id,color_to_copy.color_id)
        }

    }
}

How would one solve this using SQL?

Upvotes: 0

Views: 5051

Answers (2)

wildplasser
wildplasser

Reputation: 44250

-- spoiler
INSERT INTO car_colors (car_id, color_id)
SELECT c1.id
        , co.color_id
FROM car c1
JOIN car c0 ON 1=1
JOIN car_colors co ON co.car_id = c0.id
WHERE c1. zname = 'Deluxe improved'
AND c0. zname = 'Deluxe'
        ;

UPDATE: since the requrements appear to have changed, here's a new one. CTE to the resque ...

DROP TABLE car ;
CREATE TABLE car
        ( id INTEGER NOT NULL PRIMARY KEY
        , zcode integer NOT NULL
        , zname varchar
        );
INSERT INTO car(id, zcode,zname) VALUES
(1 ,100 , 'Deluxe' )
,(10,100 ,'Deluxe improved' )
,(2 ,200 , 'Standard' )
,(20,200 , 'Standard new' )
        ;

DROP TABLE color ;
CREATE TABLE color
        ( id integer NOT NULL PRIMARY KEY
        , zname varchar
        );

INSERT INTO color(id,zname) VALUES
(2 ,'Red' ) , (3 ,'Blue' )
        ;

DROP TABLE car_colors;
CREATE TABLE car_colors
        ( id SERIAL NOT NULL PRIMARY KEY
        , car_id  integer NOT NULL REFERENCES car (id)
        , color_id  integer NOT NULL REFERENCES color (id)
        , UNIQUE (car_id,color_id)
        )
        ;
INSERT INTO car_colors (car_id, color_id) VALUES
  (1,2) , (2,2) , (2,3)
        ;

WITH carmap AS (
        SELECT c0.id AS orgcar
                , c1.id AS newcar
        FROM car c1
        -- This is an ugly join based on a substring
        JOIN car c0 ON c1.zname ~ c0.zname AND c1.id <> c0.id
        )
INSERT INTO car_colors (car_id, color_id)
SELECT cm.newcar
        , co.color_id
FROM carmap cm
JOIN car_colors co ON co.car_id = cm.orgcar
WHERE NOT EXISTS ( SELECT *
        FROM car_colors nx
        WHERE nx.car_id = cm.newcar
        AND nx.color_id = co.color_id
        )
        ;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think the query you want looks like:

insert into car_colors(car_id, color_id)
    select <deluxe improved car id>, color_id
    from car_colors
    where car_id = <deluxe car id>

This doesn't handle the id, because that should be done at the table level. You should declare the id column as a SERIAL column.

If you are concerned that the new rows are duplicates, use:

insert into car_colors(car_id, color_id)
    select <deluxe improved car id>, color_id
    from car_colors cc           
    where car_id = <deluxe car id> and
          color_id not in (select color_id from car_colors where car_id = <deluxe improved car id>)

Upvotes: 0

Related Questions