mySQL上的重复密钥多次更新

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);           
}

Answer 1

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.)