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

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

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

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.