How to check if tow columns are duplicate when INSERT data in MySQL?

Question

There is a way that MySQL to check record which is going to INSERT is duplicate or not is using:

INSERT ... ON DUPLICATE KEY UPDATE

But as the document says: the KEY to check is the primary key.And in my situation,I need to check tow fields (columns) to make sure it is duplicate or not.My table structure:

auto_id  user_id  file_id file_status
1        1        12        1
2        3        12        0
3        1        17        1
4        4        31        1
5        1        41        0
6        4        31        0
7        1        18        1
8        5        11        0
9        1        10        0

E.g:Maybe like:

ON DUPLICATE user_id,file_id UPDATE file_status = 0;

if user_id and file_id which in one record were duplicate,I will update the file_status to 0.

Thank you very much!!

[update]

So,I have to do it in several steps!?

First, find out the records exist or not:

SELECT auto_id FROM MyTable WHERE user_id='user_id' AND file_id='file_id'.

Second, decide to INSERT new record or UPDATE according to the SELECT result!

Yoram de Langen · Accepted Answer · 2011-03-27 06:50:30Z

2

You can also create a stored procedure and check if the file_id already exists or not:

drop if procedure exists check_user_files;
create procedure check_user_files
(
    in p_file_id smallint
)
begin
    declare v_success tinyint unsigned default 0;

    if not exists (select 1 from user_files where file_id = p_file_id) then

        [....] -- insert statement

    else

        [....] -- remove or update

    end if;

    select v_success as success;
end;

And where are u using auto_id for? I think its unnecessary here?

answered Mar 27, 2011 at 6:50

Yoram de Langen

5,4993 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

qinHaiXiang Over a year ago

How can I call the store procedure at multi-times to check all records!

Yoram de Langen Over a year ago

you can do it by call check_user_files(file_id) its just like a php function but then only with call before it

Ted Hopp · Accepted Answer · 2011-03-27 06:02:31Z

0

I believe that the duplicate check is for the violation of any unique index as well as the primary key. At least according to the manual page.

answered Mar 27, 2011 at 6:02

Ted Hopp

235k48 gold badges412 silver badges533 bronze badges

Comments

Saad · Accepted Answer · 2011-03-27 06:37:32Z

0

A suggestion, if you have you're main id (auto_id) on auto increment then why not make those 2 columns your primary key so if you try to enter a value that matches it will through an error

answered Mar 27, 2011 at 6:37

Saad

29k16 gold badges51 silver badges71 bronze badges

1 Comment

qinHaiXiang Over a year ago

With your suggestion,because the auto_id is different,so MySQL will not through an error.

Collectives™ on Stack Overflow

How to check if tow columns are duplicate when INSERT data in MySQL?

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related