CODEWITHSUNDEEP
mysqlsql
dm707
dm707

Reputation: 352

INSERT WHERE NOT EXISTS doesn't work when two columns have same value

I have this query that work perfectly to insert values when they doesn't exists on table:

INSERT INTO my_table (code, type, user_id)
SELECT * FROM (SELECT 11, 2, 1) as tmp
WHERE NOT EXISTS (SELECT 1 FROM my_table WHERE code = 11 AND type = 2 AND user_id = 1) LIMIT 1;

But when I have the same value for two columns it cause a duplicate column (#1060) error.

This is the query that cause the error:

INSERT INTO my_table (code, type, user_id)
SELECT * FROM (SELECT 11, 1, 1) as tmp
WHERE NOT EXISTS (SELECT 1 FROM my_table WHERE code = 11 AND type = 1 AND user_id = 1) LIMIT 1;

I tried some variations of that code but I can't get it working.

The problem is this line:

...
SELECT * FROM (SELECT 11, 1, 1) as tmp
...

I know that the error makes sense. But I need a way to avoid it.

You can run the above query to see the error.

Any ideas about how can we solve this?

If you know a different way to insert values when they doesn't exists, I'll appreciate it too.

Upvotes: 0

Views: 64

Answers (1)

P.Salmon
P.Salmon

Reputation: 17590

The full error is ERROR 1060 (42S21): Duplicate column name '1' and that is true

MariaDB [sandbox]> SELECT * FROM (SELECT 11, 1, 1) as tmp;
ERROR 1060 (42S21): Duplicate column name '1'

The problem then is that in the sub query the column name is acquired from the value in the absence of an alias. so either provide an alias

SELECT * FROM (SELECT 11 c1, 1 c2, 1 c3) as tmp

or just select

SELECT 11, 1, 1

I'm not clear why you would want tmp?

Upvotes: 3

Related Questions