Currently I have several sql queries that look like this:
INSERT INTO `test` (col1, col2)
SELECT * FROM (SELECT '1','2') AS tmp
WHERE NOT EXISTS (
SELECT * FROM `test`
WHERE col1='1' AND col2='2'
) LIMIT 1;
INSERT INTO `test` (col1, col2)
SELECT * FROM (SELECT '3','4') AS tmp
WHERE NOT EXISTS (
SELECT * FROM `test`
WHERE col1='3' AND col2='4'
) LIMIT 1;
Generally speaking, as rule of thumb the less queries the better which is why I want to rewrite the sql queries into one query. This should make it even faster when having millions of datasets as the WHERE
clause would only iterate through the table once, hence this should be possible:
WHERE col1 in ('1','3') AND col2 in ('2','4')
But when I try multiple select statements in a single query like:
SELECT * FROM
(SELECT '1','2') AS tmp1,
(SELECT '3','4') AS tmp2
SQL throws this error:
#1136 - Column count doesn't match value count at row 1
The problem is with SELECT * FROM (SELECT '1','2') AS tmp
.
How can I use several tuples (1, 2) (3, 4)
in the subquery for it meet the WHERE
condition?
Is there a shorter and faster way to achieve this?
Any feedback is highly appreciated!
Please ignore the data types; the numbers should be formatted as int
not as varchar
.
UPDATE
When executing the following:
INSERT INTO `test` (col1, col2)
SELECT * FROM
((SELECT * FROM (SELECT '7','8') AS tmp
WHERE NOT EXISTS (
SELECT * FROM `test`
WHERE col1='7' AND col2='8'
) LIMIT 1)
UNION
(SELECT * FROM (SELECT '9','9') AS tmp
WHERE NOT EXISTS (
SELECT * FROM `test`
WHERE col1='9' AND col2='9'
) LIMIT 1)) t1;
SQL throws: Duplicate column name '9'
Best Answer
You can use
UNION ALL
to build as many rows as you need:See fiddle here.
After 10.3.3 MariaDB (sort of) supports table-valued constructors, you won't have the ability to name the columns like you would otherwise: