MySQL 5.67 - Inserting values if not exists in table not working

Question

I have a table with no primary keys and two columns. When the table is empty this query should generate a record and when this record already exists it should not.

INSERT INTO traffic (traffic_time, traffic_amount) 
SELECT '13:00:00', '24' FROM traffic
WHERE NOT EXISTS (SELECT * FROM traffic WHERE traffic_time = '13:00:00' AND 
traffic_amount = '24')

However when running this no rows are inserted even if there are no duplicates.

I tried using other answers like:

INSERT INTO traffic (traffic_time, traffic_amount)
SELECT '13:00:00','24'
WHERE NOT EXISTS (SELECT * FROM traffic WHERE traffic_time = '13:00:00' AND 
traffic_amount = '24') LIMIT 1

But this one gives me a "Error Code 1064 SQL Syntax Error" even though it is syntactically correct.

How can I resolve this issue without adding a primary key?

Note: I have looked at similar S.O. threads but am unable to resolve this issue. These answers are from those threads

Possible duplicate of [MySQL: Insert record if not exists in table](https://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table) — Bor Laze, Jun 01 '17 at 17:59

Bor Laze · Accepted Answer · 2017-06-01T20:34:58.793

1

Following link from my comment...

INSERT INTO traffic (t1, t2)
  SELECT *
  FROM (SELECT
          '24' as tmp1,
          '24' as tmp2) as tmp
  WHERE NOT EXISTS(
      SELECT t1, t2
      FROM traffic
      WHERE t1 = '24' AND
            t2 = '24'
  )
  LIMIT 1;

edited Jun 01 '17 at 20:34

answered Jun 01 '17 at 18:13

Bor Laze

2,350
10
19

This doesn't seem to work if there are columns with the same values. e.g. if I change your code to: https://pastebin.com/nVpeLGB1 it gives error code 1060 – Apparently Im user2212428 Jun 01 '17 at 19:02
Is there any way to modify your query to cater to this? – Apparently Im user2212428 Jun 01 '17 at 19:03
Whoops I linked the wrong one originally. Both numbers should be 24 – Apparently Im user2212428 Jun 01 '17 at 19:05
Query modified, take it – Bor Laze Jun 01 '17 at 20:36

MySQL 5.67 - Inserting values if not exists in table not working

1 Answers1