Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert statement that checks for duplicate before insert

I need to make a insert but only if similar record don't exists
for example:

INSERT INTO requests ('user_id','subject','text','time') VALUES (56,'test','test 1234',6516516)

but to check if there are same 'subject' and 'text' in another record to:

  1. not insert anything
  2. update 'time' and 'user_id'

I need sql for both cases because I'm no sure at this moment what I'm going to use.
Thanks in advance!

like image 854
T1000 Avatar asked Nov 29 '11 14:11

T1000


People also ask

How do you prevent duplicates while inserting in SQL?

Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.

How do you insert duplicate values?

The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.


2 Answers

INSERT INTO requests ('user_id','subject','text','time') 
VALUES (56,'test','test 1234',6516516)
ON DUPLICATE KEY UPDATE time = VALUES(time), user_id = VALUES(user_id)

Have the relevant columns set to index UNIQUE.

This will insert a row, but if subject or text (or both) already exist, you instead update the existing row with given time and user_id

like image 52
Robin Castlin Avatar answered Oct 14 '22 17:10

Robin Castlin


First, you can do the update. If the record doesn't exist, nothing will happen...

UPDATE
  requests
SET
  user_id = 56,
  time = 6516516
WHERE
  subject = 'test'
  AND text = 'test 1234'

Then you can use SELECT instead of VALUES in the INSERT. If the record already exists, nothing will be inserted...

INSERT INTO
  requests (
    user_id,
    subject,
    text,
    time
  )
SELECT
  56,
  'test',
  'test 1234',
  6516516
WHERE
  NOT EXISTS (SELECT * FROM requests WHERE subject = 'test' AND text = 'test 1234')
like image 4
MatBailie Avatar answered Oct 14 '22 17:10

MatBailie