mysql/php insert/update on duplicate key

Question

I have a product info table with more than 130 columns/fields.

I want to write a php script that adds a new product to the table OR updates the existing product if it already exist. The first field is the product key.

The product information is stored in a numbered php array : $product_info[0] to $product_info[130].

Basically something like this :

INSERT INTO table (a,b,c) VALUES ($product_info[0],$product_info[1],$product_info[2])
  ON DUPLICATE KEY UPDATE a='$product_info[0]', b='$product_info[1]', c='$product_info[2]'

Is there something more efficient than typing each of the 130 fields twice?

Why on earth would you have 130 columns in a table? Or is your terminology wrong in that you want to insert 130 rows / items? — Jim
– Jim, Commented Aug 13, 2010 at 15:30
As @premiso implies, this probably isn't ideal from a schema perspective. What does this data represent? — John Parker
– John Parker, Commented Aug 13, 2010 at 15:31
No its over 130 columns of product details... Not my choice. — Enkay
– Enkay, Commented Aug 13, 2010 at 16:41

ircmaxell · Accepted Answer · 2012-08-21 14:02:58Z

10

Yes, there is, use the VALUES() function:

INSERT INTO `table` (a, b, c) VALUES (?, ?, ?)
    ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES (b), c = VALUES(c)

Basically, in the UPDATE part, VALUES(column) will return the specified value for that column for the current row in question. So you can do interesting things like:

ON DUPLICATE KEY UPDATE 
    a = VALUES(a), 
    b = VALUES(b) + VALUES(c),

The beauty of that syntax, is it also supports multiple insert rows:

INSERT INTO `table` (a, b, c) 
    VALUES (?, ?, ?), 
    VALUES (?, ?, ?), 
    VALUES (?, ?, ?)
    ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES (b), c = VALUES(c)

edited Aug 21, 2012 at 14:02

answered Aug 13, 2010 at 15:32

ircmaxell

166k36 gold badges269 silver badges316 bronze badges

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

2 Comments

Enkay Over a year ago

Thanks for your reply but I would still have to type all 130 fields twice. This is basically the solution I posted in my question with a little twist. I'm looking for something more efficient if it's doable.

TRiG Over a year ago

Typo: KYE should be KEY. This is a single-letter change, so I can't do it myself.

Chris · Accepted Answer · 2010-08-13 15:29:18Z

1

Unfortunately MySQL does not support merging... having an ORM can help ease the pain of coding multiple IF EXISTS UPDATE ... ELSE INSERT code

answered Aug 13, 2010 at 15:29

Chris

1,0668 silver badges9 bronze badges

Comments

TRiG · Accepted Answer · 2010-08-13 15:52:11Z

0

REPLACE

answered Aug 13, 2010 at 15:52

TRiG

10.7k9 gold badges62 silver badges113 bronze badges

4 Comments

ircmaxell Over a year ago

No. Don't use REPLACE if you care about referential integrity. It deletes if exists, then inserts. So it'll destroy any foreign key relations.

Enkay Over a year ago

Right now the php code checks if the row exists. If it does not exist, it does an INSERT. If it does exist, it does a DELETE query followed by an INSERT query.

dgw Over a year ago

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.

TRiG Over a year ago

@dgw. I never do link-only answers these days. I know better now. Question: Is this answer worth salvaging, or should it just be deleted? I'm busy for the next couple of days, and then on holiday till the end of the month, so I'm not going to fix it any time soon.

Collectives™ on Stack Overflow

mysql/php insert/update on duplicate key

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related