Jump to content

Insert Ignore


Go to solution Solved by Barand,

Recommended Posts

Hi

I need to insert the data in the database but on the condition that the values of two columns combined do not already exist in the table? Is it possible to use inset ignore for this?

example

Current data in the table for id and timestamp.

id  | timestamp

57 | 2023-08-15 09:07:13

It should not be possible to insert again the same values combined;

57 | 2023-08-15 09:07:13

 But it should be fine to insert:

58 | 2023-08-15 09:07:13

57 | 2023-08-16 09:07:13

etc.

If that makes sense?

Edited by cornacum
Link to comment
https://forums.phpfreaks.com/topic/317189-insert-ignore/
Share on other sites

If id in this instance is a foreign key, then you also have a poor column naming convention.

Does your table have a unique constraint on this pair of columns? EG

UNIQUE KEY (id, timestamp)

If so, that will prevent a duplicate being inserted. INSERT IGNORE will prevent the attempt from throwing an error message.

Link to comment
https://forums.phpfreaks.com/topic/317189-insert-ignore/#findComment-1611160
Share on other sites

The table has 3 columns. 

No there are no constraints on user_id and timestamp, so I assume insert ignore will not work.

id - primary, unique

user_id

timestamp

Is there any other way around this?

The only thing I can think of is, before I run Insert Query, run  "SELECT * from table_name WHERE user_id = user_id AND timestamp = timestamp", if I get row count > 0, there is a duplicate and do not insert ?

 

 

 

 

 

 

Link to comment
https://forums.phpfreaks.com/topic/317189-insert-ignore/#findComment-1611187
Share on other sites

  • Solution

Just alter the table to add the unique constraint. Run this query...

ALTER TABLE `tablename` 
ADD UNIQUE INDEX `unq_user_date` (`user_id` ASC, `timestamp` ASC);

Checking if it exists first is the worst way to do it.

Link to comment
https://forums.phpfreaks.com/topic/317189-insert-ignore/#findComment-1611191
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.