How to Perform an UPSERT so that I can use both new and old values in update part

Question

Stupid but simple example: Assume I have a table 'Item' where I keeps totals of the items that receive.

Item_Name              Items_In_Stock 

Item name is primary key here. How to i achieve the following when ever I receive item A in quantity X.

If the item does not exist, I insert a new recored for Item A and set the items in stock to X and if there exists a record where items in stock was Y then the new value in items in stock is (X + Y)

INSERT INTO `item` (`item_name`, items_in_stock) VALUES( 'A', 27) ON DUPLICATE KEY UPDATE `new_items_count` = 27 + (SELECT items_in_stock where item_name = 'A' ) 

My problem is that i have multiple column in my actual table. Is it a good idea to write multiple select statements in the update part?

Of course I can do it in code but is there a better way?

Answer 1

As mentioned in my comment, you don't have to do the subselect to reference to the row that's causing ON DUPLICATE KEY to fire. So, in your example you can use the following:

INSERT INTO `item` (`item_name`, items_in_stock) VALUES( 'A', 27) ON DUPLICATE KEY UPDATE `new_items_count` = `new_items_count` + 27 

Remember that most things are really simple, if you catch yourself overcomplicating something that should be simple then you are most likely doing it the wrong way :)

Answer 2

You can get idea from this example:

Suppose you want to add user wise seven days data

It should have unique value for userid and day like

UNIQUE KEY `seven_day` (`userid`,`day`) 

Here is the table

CREATE TABLE `table_name` (   `userid` char(4) NOT NULL,   `day` char(3) NOT NULL,   `open` char(5) NOT NULL,   `close` char(5) NOT NULL,   UNIQUE KEY `seven_day` (`userid`,`day`) ); 

And your query will be

INSERT INTO table_name (userid,day,open,close)      VALUES ('val1', 'val2','val3','val4')          ON DUPLICATE KEY UPDATE open='val3', close='val4'; 

Example:

<?php //If your data is $data= array(         'sat'=>array("userid"=>"1001", "open"=>"01.01", "close"=>"11.01"),         'sun'=>array("userid"=>"1001", "open"=>"02.01", "close"=>"22.01"),         'sat'=>array("userid"=>"1001", "open"=>"03.01", "close"=>"33.01"),         'mon'=>array("userid"=>"1002", "open"=>"08.01", "close"=>"08.01"),         'mon'=>array("userid"=>"1002", "open"=>"07.01", "close"=>"07.01")     );   //If you query this in a loop //$conn = mysql_connect("localhost","root",""); //mysql_select_db("test", $conn);  foreach($data as $day=>$info) {     $sql = "INSERT INTO table_name (userid,day,open,close)                  VALUES ('$info[userid]', '$day','$info[open]','$info[close]')              ON DUPLICATE KEY UPDATE open='$info[open]', close='$info[close]'";     mysql_query($sql); } ?> 

Your data will be in table:

+--------+-----+-------+-------+ | userid | day | open  | close | +--------+-----+-------+-------+ | 1001   | sat | 03.01 | 33.01 | | 1001   | sun | 02.01 | 22.01 | | 1002   | mon | 07.01 | 07.01 | +--------+-----+-------+-------+