Search code examples
mysqlarraysinserttemp-tables

Mysql insert array from another table and fill the rest with default values

here's my question:

I have a table TRUST(id INT, trustlevel INT) which contains records (1, 5) and (2, 3). I also have an array containing ids (1, 2, 3, 4, 5).

What I want to achieve is to insert all the ids from the array into a temporary table called PRESELECTED(id INT, trustlevel INT DEFAULT 10) in such a way that the ids with trustlevels which are in TRUST to be copied and the rest of the array to accept the default value which is 10 in this case. So at the end PRESELECTED will have records (1, 5), (2, 3), (3, 10), (4, 10), (5, 10).

I have figured out how to put the values from the TRUST table but I am stuck with the rest:

CREATE TEMPORARY TABLE PRESELECTED (id INT, trustlevel INT DEFAULT 10);

INSERT INTO PRESELECTED (
  SELECT * FROM TRUST WHERE id IN (1, 2, 3, 4, 5)
)

The problem with this is that it only inserts 1 and 2 because 3, 4 and 5 are not in TRUST

Any quick suggestions?

Solution

    1. Add unique key on ID ( PK or UNIQUE KEY )

    2. Insert the existing ids from TRUST first

      INSERT INTO PRESELECTED SELECT id,trustlevel FROM TRUST WHERE id IN(1,2,3,4,5);

    3. Fill the missing ids

      INSERT IGNORE INTO PRESELECTED (id) VALUES (1),(2),(3),(4),(5);