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?

Why would you use subquery in your example? You could have just used `ON DUPLICATE KEY UPDATE new_items_count = new_items_count + 27`. Seeing you didn't post other columns, it's hard to answer your question because the expected behavior is unknown. What are you doing with other columns? Updating some numbers or? — Michael J.V., May 24 '11 at 08:54
Great. Thats answers the question. I didn't know that I can use column name instead of sub query. If you post it as an answer I will accept it as the answer. — WPFAbsoluteNewBie, May 24 '11 at 09:25
There's a ruby library that defines a MySQL function: https://github.com/seamusabshere/upsert — Seamus Abshere, Feb 25 '14 at 02:31
what is `new_items_count` here? is it another column on your table? — asgs, May 07 '16 at 18:58

score 174 · Accepted Answer · answered May 24 '11 at 09:39

174

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

answered May 24 '11 at 09:39

Michael J.V.

5,499
1
20
16

2

**Note:** It looks like the ON DUPLICATE KEY UPDATE clause will increase the auto-increment value if the PK field is auto-increment, regardless of which effect (INSERT or UPDATE) was triggered (when using InnoDB engine). See: [MySQL Reference Manual](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html) – alejandro Aug 30 '18 at 23:04
2

To prevent code duplication, you should use `new_items_count = new_items_count + VALUES(items_in_stock)` – Paul Spiegel Jul 25 '19 at 07:29
2

Can this be done using `REPLACE` query? It's much more simple that way right – Shankar Thyagarajan Sep 09 '19 at 06:44
^^ "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 " – nom-mon-ir Aug 11 '20 at 01:28

score 16 · Answer 2 · edited Sep 29 '19 at 14:40

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.

Shouldn't the final value be `301.0` and not `302.0`? – alejandro Aug 30 '18 at 23:06 — alejandro, Aug 30 '18 at 23:06
This is a good and explanatory answer – vidit mathur May 03 '22 at 13:38 — vidit mathur, May 03 '22 at 13:38

score 13 · Answer 3 · edited Sep 06 '13 at 08:04

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

Never concatenate data directly into a query. Use prepared/parameterized queries with PDO or similar. In the case of this example, it would have been appropriate to at least escape data. — Brad, May 18 '14 at 06:18
Note that this code is using the `mysql_*` functions which were removed in PHP 7; when considering a replacement, see [this interesting conundrum](http://php.net/manual/en/mysqlinfo.api.choosing.php#120388) on PDO vs MySQLi. — i336_, Jan 14 '17 at 22:06

score 1 · Answer 4 · edited Feb 03 '17 at 14:03

1

This is the syntax for an upsert

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

edited Feb 03 '17 at 14:03

MikeT

5,398
3
27
43

answered Nov 16 '13 at 21:34

relipse

1,730
1
18
24

score 1 · Answer 5 · answered Dec 25 '18 at 23:12

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

What does it do? Does it ignore the entire row? Or the unique column(s) only? Please expand your answer — TheRealChx101, Sep 21 '21 at 20:00

score 0 · Answer 6 · answered Nov 22 '19 at 08:34

0

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

answered Nov 22 '19 at 08:34

Justin J

808
8
14

This one is for Postgres, not MySQL. :-) – Andrey Tarantsov Apr 30 '21 at 18:10

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

6 Answers6

Linked

Related