MYSQL Conditional Insert Into with multiple rows

Question

Is it possible to insert multiple rows while checking each new row against a condition? I know that you can run a simple insert into with multiple rows

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6),(7,8,9)

AND

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6),(7,8,9)
ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b), c=VALUES(c)

I am attempting to do something like this

INSERT INTO x_table(a,b,c) 
    SELECT VALUES(a),VALUES(b),VALUES(c)
        FROM y_table
        WHERE NOT EXISTS (SELECT * FROM x_table
                             WHERE a != VALUES(a) 
                               AND b != VALUES(b))

Test Example (Tested and Working)

INSERT INTO `table` (id, timestamp, v1, v2, v3, v4, v5)
SELECT id, timestamp, v1, v2, v3, v4, v5
FROM (SELECT 6783 AS id, FROM_UNIXTIME('1580194194') AS timestamp, 1 AS v1, 0 AS v2, 1 AS v3, 0 AS v4, 45 AS v5
      UNION ALL
      SELECT 6845,FROM_UNIXTIME('1580194194'),1,0,1,0,107
      UNION ALL
      SELECT 6973,FROM_UNIXTIME('1580194194'),1,0,1,0,234
      ) y
WHERE NOT EXISTS (SELECT * FROM `table` x WHERE x.v1 = y.v1 AND x.v3 = y.v3 AND x.v5 = y.v5 ORDER BY TIMESTAMP DESC LIMIT 1)

'Is it possible to insert multiple rows ' - no it isn't you can only insert 1 row at a time to a table. — P.Salmon
– P.Salmon, Commented Jan 28, 2020 at 5:11

Nick · Accepted Answer · 2020-01-28 04:05:03Z

3

You can write something like this:

INSERT INTO x_table
SELECT a, b, c
FROM (SELECT 1 AS a, 2 AS b, 5 AS c
      UNION ALL
      SELECT 4, 5, 6
      UNION ALL
      SELECT 7, 8, 9
      ) y
WHERE NOT EXISTS (SELECT * FROM x_table x WHERE x.a = y.a AND x.b = y.b)

Note that I think your EXISTS clause needs to use =, not != otherwise it will be true unless there is only 1 row in x_table which has matching a and b values to the first row to be inserted.

Demo on dbfiddle

edited Jan 28, 2020 at 4:05

answered Jan 28, 2020 at 3:48

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Eric M Over a year ago

When attempting this query I get "#1136 - Column count doesn't match value count at row 1". See updated test example above.. i will fiddle with it and see if i can find out why

Nick Over a year ago

@EricM you're missing AS id from the first row of the data i.e. SELECT 6783 AS id, ...

Nick Over a year ago

@EricM note it doesn't make any sense to have ORDER BY and LIMIT in an EXISTS query. EXISTS only cares if there is 1 row that matches the condition so whether you order the rows or limit them makes no difference.

Eric M Over a year ago

I found that this solution does not work with auto increment index columns as it requires have all fields present in the query for this to work. I removed the index column and my problem went away. Any recommendations?

Nick Over a year ago

@EricM add the column names to the query before insert and leave id out of the SELECT i.e. INSERT INTO `table` (timestamp, v1, v2, v3, v4, v5) SELECT timestamp, v1, v2, v3, v4, v5 FROM (SELECT ...)

|

fifonik · Accepted Answer · 2020-01-28 04:09:33Z

0

You can do some checks in before insert trigger. Something like this:

CREATE TRIGGER `TRG_x_table_before_insert` BEFORE INSERT ON `x_table` FOR EACH ROW BEGIN
    SET @found = NULL;

    SELECT
        a INTO @found
    FROM
        y_table
    WHERE
            a = NEW.a
        AND b = NEW.b
    ;

    IF @found IS NOT NULL THEN
        SET @msg = 'Insert failed';
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
    END IF;
END

answered Jan 28, 2020 at 4:09

fifonik

1,6561 gold badge11 silver badges20 bronze badges

Collectives™ on Stack Overflow

MYSQL Conditional Insert Into with multiple rows

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related