CODEWITHSUNDEEP
mysql
Apparently Im user2212428
Apparently Im user2212428

Reputation: 99

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

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

Upvotes: 0

Views: 37

Answers (1)

Bor Laze
Bor Laze

Reputation: 2516

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;

Upvotes: 1

Related Questions