CODEWITHSUNDEEP
mysqlsqlinsertduplicates
T1000
T1000

Reputation: 2941

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!

Upvotes: 6

Views: 28319

Answers (6)

Ashwin Balani
Ashwin Balani

Reputation: 771

Replace your query from

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

To,

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

This will add a small delay to the execution, but it is more efficient than any other method

Upvotes: 1

Hemath
Hemath

Reputation: 31

You can use IGNORE command

For example, use like this

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

instead of, 

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

Upvotes: 3

MatBailie
MatBailie

Reputation: 86706

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

Upvotes: 6

Anja
Anja

Reputation: 324

IF NOT EXISTS (SELECT user_id, text from users where user_id = @user_id AND text = @text) BEGIN
   INSERT INTO users ....
ELSE
   UPDATE users SET xyz=uvw where user_id = @user_id AND text = @text
END

Upvotes: 1

Robin Castlin
Robin Castlin

Reputation: 10996

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

Upvotes: 17

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143061

Look at the REPLACE mysql statement?

Upvotes: 0

Related Questions