CODEWITHSUNDEEP
phpmysqlsql
mk_89
mk_89

Reputation: 2742

mySQL on duplicate key multiple update

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

Upvotes: 1

Views: 727

Answers (2)

spencer7593
spencer7593

Reputation: 108390

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

Upvotes: 5

mlishn
mlishn

Reputation: 1667

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

Upvotes: 2

Related Questions