MYSQL, multiple insert and ON DUPLICATE UPDATE

Question

I'm really blocked with multiple insert values and if any one exist, do custom update. Here is my query that not work :

INSERT INTO `table` (f1,f2,status) VALUES 
(1,5,'on') ON DUPLICATE KEY UPDATE status='off', 
(3,2,'on') ON DUPLICATE KEY UPDATE status='off', 
(15,20,'on') ON DUPLICATE KEY UPDATE status='off';

There is any solution that can do this query?

Thanks for everyone

with custom update you want specific fields update or removing old values with new one ? — Mahmood Rehman
– Mahmood Rehman, Commented Jun 25, 2015 at 11:22

Gordon Linoff · Accepted Answer · 2015-06-25 11:19:46Z

1

You can only have one ON DUPLICATE KEY per INSERT:

INSERT INTO `table`(f1, f2, status)
    SELECT 1 ,5, 'on' UNION ALL 
    SELECT 3, 2, 'on' UNION ALL
    SELECT 15, 20, 'on'
    ON DUPLICATE KEY UPDATE status = 'off';

(Of course, you can also do this with VALUES; I just prefer SELECT because it is more general.)

answered Jun 25, 2015 at 11:19

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

CodingHero Over a year ago

Gordon, how it's possible to add IF also? ON DUPLICATE KEY UPDATE status = 'off' IF status= 'on';

Gordon Linoff Over a year ago

@user3308323 . . . I don't think that is necessary if status only takes on two values.

Collectives™ on Stack Overflow

MYSQL, multiple insert and ON DUPLICATE UPDATE

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related