mySQL on duplicate key multiple update

Question

im trying to create a simple SQL INSERT otherwise UPDATE statement but to be honest I don't know what im doing

here is my function so far

function addInventoryBook($isbn, $sku, $price, $quantity, $condition, $dateOpened){
   $q = "INSERT INTO ".TBL_INVENTORY." VALUES('$isbn', '$sku', $price, 
   $quantity, '$condition', $dateOpened)
   ON DUPLICATE KEY UPDATE VALUES('$isbn', '$sku', $price, $quantity, 
   '$condition', $dateOpened)";     

   return mysql_query($q, $this->connection);           
}

a previous function which seemed that update the price field was working

function addInventoryBook($isbn, $sku, $price, $quantity, $condition, $dateOpened){
   $q = "INSERT INTO ".TBL_INVENTORY." VALUES('$isbn', '$sku', $price, 
   $quantity, '$condition', $dateOpened)
   ON DUPLICATE KEY UPDATE price = $price";     

   return mysql_query($q, $this->connection);           
}

try ON DUPLICATE KEY UPDATE SET price = VALUES(price), sku = VALUES(sku) ,quantity = VALUES(quantity), ....., you might need to add the field names between ".TBL_INVENTORY." and VALUES. — Vatev
– Vatev, Commented Jul 24, 2012 at 18:06
echo your query before you run it, make sure it's actually what you think it is. — WWW
– WWW, Commented Jul 24, 2012 at 18:14
There is no "SET" anywhere as part of the correct mySQL syntax. (It was also in the original question and then fixed.) — Eric deRiel
– Eric deRiel, Commented Jul 24, 2012 at 18:24

spencer7593 · Accepted Answer · 2012-07-24 18:42:18Z

5

I'd write it like this, rather than supplying each of the values a second time:

ON DUPLICATE KEY UPDATE 
  isbn       = VALUES(isbn)
  sku        = VALUES(sku)
  price      = VALUES(price)
  quantity   = VALUES(quantity)
  condition  = VALUES(condition)
  dateOpened = VALUES(dateOpened)

(That will assign the values that would have been inserted, if the insert had succeeded.)

(You likely don't want assign values to whichever columns make up the "unique" key that was violated by the insert; I've included all the columns here because that's what you show in your attempt.)

edited Jul 24, 2012 at 18:42

answered Jul 24, 2012 at 18:16

spencer7593

109k15 gold badges122 silver badges148 bronze badges

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

1 Comment

spencer7593 Over a year ago

@Vatev: very good point about this pattern working for statements that insert multiple rows. (This answer is the same answer you left as a comment; I upvoted your comment, I would upvote your answer if you leave one.)

mlishn · Accepted Answer · 2012-07-24 18:14:37Z

2

Assuimg TBL_INVENTORY is a string with the column values:

$q = "INSERT INTO ".TBL_INVENTORY." VALUES('$isbn', '$sku', $price,     $quantity, '$condition', $dateOpened) ON DUPLICATE KEY UPDATE isbn='$isbn', sku='$sku', price='$price', quantity='$quantity', condition='$condition', dateOpened='$dateOpened'";

Reference link : http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

answered Jul 24, 2012 at 18:14

mlishn

1,66714 silver badges19 bronze badges

4 Comments

WWW Over a year ago

You don't have to include every single field in the update portion of the query.

WWW Over a year ago

My point is maybe the asker only would want to update the price in an update condition.

mlishn Over a year ago

you're right, I just did it for consistency. @mk_89 will use it as he/she pleases. I thought you meant there was a sql function go around this..

mk_89 Over a year ago

I think I have tried this before and I remember that it does not work because I could not update more than 3 fields this way, I usually get the same problem when using OR statements.

Collectives™ on Stack Overflow

mySQL on duplicate key multiple update

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related