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

Question

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'
    )

what is your MySQL version ? – Alpesh Jikadra Mar 28 '18 at 13:50 — Alpesh Jikadra, Mar 28 '18 at 13:50
I have added the answer you can check – Alpesh Jikadra Mar 28 '18 at 14:01 — Alpesh Jikadra, Mar 28 '18 at 14:01
Could you please accept one of the answer ? – Alpesh Jikadra Mar 28 '18 at 16:05 — Alpesh Jikadra, Mar 28 '18 at 16:05

score 1 · Answer 1 · answered Mar 28 '18 at 13:40

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'
    )

Thanks, but did not work for me. Throws sql syntax error. – WGS Mar 28 '18 at 14:00 — WGS, Mar 28 '18 at 14:00

MarcinEl · Answer 2 · 2018-03-28T14:24:49.657

1

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

edited Mar 28 '18 at 14:24

answered Mar 28 '18 at 13:43

MarcinEl

171
1
5

Tried this from the link you provided - but also throws syntax error. IF NOT EXISTS ( SELECT 1 FROM tasks_admin2agent WHERE trans_id = '2186597-L1' AND task_name = 'Incomplete Listing Record' ) BEGIN 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') END; – WGS Mar 28 '18 at 14:05

score 1 · Answer 3 · answered Mar 28 '18 at 13:57

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

score 1 · Answer 4 · answered Mar 28 '18 at 14:42

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.

score 0 · Accepted Answer · answered Mar 28 '18 at 15:41

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

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

5 Answers5