CODEWITHSUNDEEP
mysqlmariadb
sk17261205
sk17261205

Reputation: 451

How to resolve Duplicate column name error in MariaDb

I am trying to run the below query in HeidiSQL-

INSERT INTO entities (id, name, description, port) SELECT * FROM 
(SELECT 1, 'customer', 'Customer', 'xyz') AS tmp 
WHERE NOT EXISTS 
( SELECT name FROM entities WHERE name = 'customer' AND port = 'xyz' ) LIMIT 1

I want to insert the above values in entities table if not exists. But I am getting this error- Duplicate column name 'Customer'

Any idea how to resolve this? Appreciate any help.

Upvotes: 0

Views: 471

Answers (2)

Hack Dawg
Hack Dawg

Reputation: 108

Try this (note that the column 'id' is the primary key):

INSERT INTO entities (name, description, port) 
SELECT 'customer', 'Customer', 'xyz' FROM entities 
WHERE name NOT IN ( SELECT name FROM entities WHERE name = 'customer' AND port = 'xyz' ) LIMIT 1

Upvotes: 0

Shadow
Shadow

Reputation: 34231

Add aliases to the select oart of the query:

INSERT INTO entities (id, name, description, port) SELECT * FROM 
(SELECT 1, 'customer' as name, 'Customer' as description, 'xyz') AS tmp 
WHERE NOT EXISTS 
( SELECT name FROM entities WHERE name = 'customer' AND port = 'xyz' ) LIMIT 1

However, I would rather create a unique index on name - port fields, which would prevent duplicate records from being created.

Upvotes: 1

Related Questions