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

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?

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

Although Michael's answer is the right one, you need to know a bit more to do the upsert programmatically:

First, create your table and specify which columns you want a unique index on:

CREATE TABLE IF NOT EXISTS Cell (
  cellId BIGINT UNSIGNED,
  attributeId BIGINT UNSIGNED,
  entityRowId BIGINT UNSIGNED,
  value DECIMAL(25,5),
  UNIQUE KEY `id_ce` (`cellId`,`entityRowId`)
)

Then insert some values into it:

INSERT INTO Cell VALUES( 1, 6, 199, 1.0 );

Try doing the same thing again, and you'll get a duplicate key error, because cellId and entityRowId are same:

INSERT INTO Cell VALUES( 1, 6, 199, 1.0 );

> Duplicate entry '1-199' for key 'id_ce'

That's why we use the upsert command:

INSERT INTO Cell ( cellId, attributeId, entityRowId, value)
VALUES( 1, 6, 199, 300.0 )
ON DUPLICATE KEY UPDATE `value` = `value` + VALUES(`value`)

This command takes the value 1.0 that's already there as value and does a value = value + 300.0.

So even after executing the above command, there will be only one row in the table, and the value will be 301.0.

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 |
+--------+-----+-------+-------+

This is the syntax for an upsert

INSERT INTO `{TABLE}` (`{PKCOLUMN}`, `{COLUMN}`) VALUES (:value)
ON DUPLICATE KEY UPDATE `{COLUMN}` = :value_dup';

If you have value for PK Column, or Unique Index on a column which satisfies unicity, You can use INSERT IGNORE , INSERT INTO ... ON DUPLICATE, or REPLACE

Example with INSERT IGNORE

INSERT IGNORE INTO Table1
    (ID, serverID, channelID, channelROLE)
VALUES
    (....);

Example with INSERT INTO .. ON DUPLICATE KEY UPDATE

SET @id = 1,
    @serverId = 123545,
    @channelId = 512580,
    @channelRole = 'john';
INSERT INTO Table1
    (ID, serverID, channelID, channelROLE)
VALUES
    (@id, @serverId, @channelId, @channelRole)
ON DUPLICATE KEY UPDATE
    serverId = @serverId,
    channelId = @channelId,
    channelRole = @channelRole;

Example with Replace

REPLACE INTO table1
    (ID, serverID, channelID, channelROLE)
VALUES
    (...);

Example for upsert

INSERT INTO table1 (col1, col2, col3)
VALUES ($1, $2, $3)
ON CONFLICT (col1)
DO
UPDATE
SET col2 = $2, col3 = $3
WHERE col1 = $1
RETURNING col1