Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
6  
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? – Brad F Jacobs Aug 13 '10 at 15:30
As @premiso implies, this probably isn't ideal from a schema perspective. What does this data represent? – middaparka Aug 13 '10 at 15:31
No its over 130 columns of product details... Not my choice. – Enkay Aug 13 '10 at 16:41

3 Answers

up vote 5 down vote

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)
share|improve this answer
1  
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. – Enkay Aug 13 '10 at 16:44
Typo: KYE should be KEY. This is a single-letter change, so I can't do it myself. – TRiG Aug 21 '12 at 11:50
up vote 1 down vote

REPLACE

share|improve this answer
1  
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. – ircmaxell Aug 13 '10 at 16:11
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. – Enkay Aug 13 '10 at 16:47
1  
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. – dgw Aug 21 '12 at 11:36
@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. – TRiG Aug 21 '12 at 11:49
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.