Insert into 2 tables with single SQL statement instead of loop

Question

I have to insert data in provonance of several table which itself comes from csv (COPY).

Before I used a LOOP in a function to enter the data. I want to simplify the thing for the sake of maintainability and speed.

I need to insert data into a description table, which serves as both the title and description (and multi language).

Previously my code was as follows (extract from the loop):

insert into description (label, lang_id, poi_id,date_dernier_update, date_enregistrementbdd, date_derniere_lecture) values (label, lang_id, poi_id, now(), now(), now()) RETURNING id INTO _retour_id_titre;
insert into poi_titre_poi (poi_id, titre_poi_id, titre_poi_key) values (poi_id, _retour_id_titre, label_lang);

But now I can't:

with rows as (
insert into description (label, lang_id, poi_id)
select rdfslabelfrs, '1',  (select id from poi where uri_id = csv_poi_rdf_fr.poi) as toto from csv_poi_rdf_fr  RETURNING id 
    )
    insert into poi_titre_poi (poi_id, titre_poi_id, titre_poi_key) 
     select description.poi_id,  id , 'fr'
    FROM description;

In fact, I cannot insert the 'poi_id' in the 'poi_titre_poi' table which corresponds to the one which was inserted in the description table.

I get this error message:

ERROR:  more than one row returned by a subquery used as an expression
État SQL : 21000

Can I make this work, or do I need to loop?

score 1 · Accepted Answer · answered Aug 13 '21 at 20:18

1

Filling in missing bits with assumptions, it could work like this:

WITH description_insert AS (
   INSERT INTO description
         (label         , lang_id, poi_id)
   SELECT c.rdfslabelfrs, 1      , p.id
   FROM   csv_poi_rdf_fr c
   JOIN   poi p ON p.uri_id = c.poi
   RETURNING poi_id, id
   )
INSERT INTO poi_titre_poi (poi_id, titre_poi_id, titre_poi_key)
SELECT d.poi_id,  d.id , 'fr'
FROM   description_insert d;

Erwin Brandstetter

605,456
145
1,078
1,228

Unless I am mistaken, the join only works if there is some notion of a key, right? – Camel4488 Aug 13 '21 at 20:25
@Camel4488: Join operations do not depend on key columns. The join condition (and additional WHERE conditions) define what is joined. – Erwin Brandstetter Aug 13 '21 at 20:28
olala I thought the join depended on the keys! you just saved me miles of line of code thank you very much! – Camel4488 Aug 13 '21 at 20:29
@Camel4488: Of course, if you join on a unique column (a "key" column), the join cannot multiply rows. You can even use an unconditional `CROSS JOIN` ... – Erwin Brandstetter Aug 13 '21 at 20:30
I did a training and my trainer told us for the inner join and all the others you need the key notions. So I never even thought of trying to join without putting keys in my tables .... (after it was a short on mysql, I don't know if it's mandatory on mysql). brief morality I should have tried ... thank you very much! – Camel4488 Aug 13 '21 at 20:32
@Camel4488: It's still good design to have a PK on every table. But you can join any way you want. – Erwin Brandstetter Aug 13 '21 at 20:43
Yes I hesitate to return to the loop (but very lon) or add PK ... but in fact my tables are "clones" of CSV or I made COPY (so I do not use these tables for the continued)) but thank you very much in any case sincerely I started to despair – Camel4488 Aug 13 '21 at 20:46

Insert into 2 tables with single SQL statement instead of loop

1 Answers1