sql join on multiple list of values

Question

I have a an sql Postgres table with a composite ID and I want to fill wide range of value on this table, if the data aren't already exist. I have a table

CREATE TABLE MyTable ( idCol1 VARCHAR(256) NOT NULL, idCol2 VARCHAR(256) NOT NULL, idCol3 VARCHAR(256) NOT NULL, amount VARCHAR(256) NOT NULL, )

And for all the value of idCol1 in ['A','B','C'], idCol2 in ['D','E','F'], idCol3 in ['G','H','I'] I want to set an amount value, if the row don't already exist.

The issue is the idCol1, idCol2 and idCol3 aren't foreing key and their value is not in any table. I have to fill those directly into the sql query.

I have trouble to write a request who simultaneously insert data, do a join on 3 differentes array of data and don't erase existing data.

How can I do-it?

Unrelated to your problem, but: varchar(256) offers no performance or storage advantages over e.g. varchar(321) or varchar(258) — user330315
– user330315, Commented Mar 5, 2020 at 14:58
Do you really store a comma separated string like ['A','B','C'] (including the square brackets) in those columns? — user330315
– user330315, Commented Mar 5, 2020 at 14:59

Gordon Linoff · Accepted Answer · 2020-03-05 14:58:29Z

Do you want to insert rows that are not present? If so:

insert into mytable (idcol1, idcol2, idcol3, amount) select i1.idcol1, i2.idcol2, i3.idcol3, 0 as amount from (values ('A'), ('B'), ('C')) i1(idcol1) CROSS JOIN (values ('D'), ('E'), ('F')) i2(idcol2) CROSS JOIN (values ('G'), ('H'), ('I')) i3(idcol3) LEFT JOIN mytable t USING (idcol1, idcol2, idcol3) where t.idcol1 IS NULL;

For information my issue was I did'nt use CROSS JOIN, only ',' to do the cartesian product. Your solution work, thank-you !
@sab . . . , IS CROSS JOIN. I strongly recommend that you use CROSS JOIN because it more clearly shows the intention of the code.

Collectives™ on Stack Overflow

sql join on multiple list of values

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related