CODEWITHSUNDEEP
mysql
WGS
WGS

Reputation: 199

Insert record if two values do not exist in MYSQL (same table)

I am trying to insert a record into a table only if there is no existing record that contains two values. This query throws error: "syntax to use near 'WHERE NOT EXISTS"

I've tried various examples from SO posts but can't seem to get this going.

INSERT INTO tasks_admin2agent 
(trans_id,category,task_name,task_detail,date_due_d) 
VALUES 
('2186597-L1','Listing','Incomplete Listing Record', 'Please complete the 
listing record','2018-03-31') 
WHERE NOT EXISTS 
   (SELECT 
    trans_id,task_name 
    FROM tasks_admin2agent 
    WHERE trans_id = '2186597-L1' 
    AND task_name = 'Incomplete Listing Record'
    )

Upvotes: 0

Views: 78

Answers (5)

WGS
WGS

Reputation: 199

Was able to get it working from this example using FROM DUAL.

Thank you all for your assistance!

How to 'insert if not exists' in MySQL?

INSERT INTO `table` (value1, value2) 
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL
WHERE NOT EXISTS (SELECT * FROM `table` 
      WHERE value1='stuff for value1' AND value2='stuff for value2') 
LIMIT 1

Upvotes: 0

dognose
dognose

Reputation: 20889

Just set a (Composite-)Key-Constraint (unique) on the two columns in question: 

ALTER TABLE tbl ADD UNIQUE KEY `uniqueConstraint` (col1, col2);

Use a regular insert and "handle" "Duplicate Key Errors" -> That means the Value-Pair already exists.

This would also allow you to combine the query with ON DUPLICATE KEY UPDATE-Syntax. (if required)

Note that aborted inserts or On duplicate key update will consume the next auto-increment id anyway.

Upvotes: 1

MarcinEl
MarcinEl

Reputation: 219

Try this:

INSERT INTO tasks_admin2agent 
(trans_id,category,task_name,task_detail,date_due_d) 
SELECT 
'2186597-L1','Listing','Incomplete Listing Record', 'Please complete the 
listing record','2018-03-31' from tasks_admin2agent
WHERE NOT EXISTS 
(SELECT 
 *
FROM tasks_admin2agent 
WHERE trans_id = '2186597-L1' 
AND task_name = 'Incomplete Listing Record'
);

You can also look at: INSERT VALUES WHERE NOT EXISTS

Upvotes: 1

Alpesh Jikadra
Alpesh Jikadra

Reputation: 1722

Try something like 

INSERT INTO tasks_admin2agent 
(trans_id,category,task_name,task_detail,date_due_d) 
SELECT 
'2186597-L1','Listing','Incomplete Listing Record', 'Please complete the 
listing record','2018-03-31' FROM tasks_admin2agent
WHERE NOT EXISTS 
   (SELECT 
    *
    FROM tasks_admin2agent 
    WHERE trans_id = '2186597-L1' 
    AND task_name = 'Incomplete Listing Record'
    );

You can try Demo

Upvotes: 1

Matthias Neubert
Matthias Neubert

Reputation: 335

For me it works if you change "VALUES" to "SELECT". But I cannot explain why this works 

INSERT INTO tasks_admin2agent 
(trans_id,category,task_name,task_detail,date_due_d) 
SELECT 
('2186597-L1','Listing','Incomplete Listing Record', 'Please complete the 
listing record','2018-03-31') FROM tasks_admin2agent
WHERE NOT EXISTS 
   (SELECT 
    trans_id,task_name 
    FROM tasks_admin2agent 
    WHERE trans_id = '2186597-L1' 
    AND task_name = 'Incomplete Listing Record'
    )

Upvotes: 1

Related Questions