CODEWITHSUNDEEP
mysql
evan_b
evan_b

Reputation: 1239

Mysql - INSERT from SELECT with conditional ON DUPLICATE KEY UPDATE

I'm reading about conditional updates on duplicate key based on IF statements - e.g., MySQL Conditional Insert on Duplicate.

I'm trying to do something similar, but within an insert from a select:

INSERT IGNORE INTO data1 (id, date, quantity)
SELECT id, date, quantity
FROM other_table
WHERE date = '2015-03-01'
AND id=123
ON DUPLICATE KEY UPDATE 
quantity = IF(quantity IS NULL, VALUES(quantity), quantity)

However, this generates an error:

#1052 - Column 'quantity' in field list is ambiguous

I can't quite figure out how to tell MySQL which 'quantity' field is which in order to resolve the ambiguity problem. Adding aliases to each table doesn't seem to help (calling data1 'd' throws a different error).

Anyone have experience with this?

Upvotes: 2

Views: 2345

Answers (3)

axiac
axiac

Reputation: 72177

You should qualify the references to the quantity field that belongs to table data1 in the ON DUPLICATE KEY UPDATE part of the query:

INSERT INTO data1 (id, date, quantity)
SELECT id, date, quantity
FROM other_table
WHERE date = '2015-03-01'
AND id=123
ON DUPLICATE KEY UPDATE 
quantity = IF(data1.quantity IS NULL, VALUES(quantity), data1.quantity)

A shorter way to write this IF() expression is to use function IFNULL() or COALESCE():

ON DUPLICATE KEY UPDATE 
quantity = IFNULL(data1.quantity, VALUES(quantity))

or

ON DUPLICATE KEY UPDATE 
quantity = COALESCE(data1.quantity, VALUES(quantity))

Also, there is no need to use IGNORE. The errors that IGNORE converts to warnings does not happen any more because of the ON DUPLICATE KEY UPDATE clause.

2024 update

The VALUES() function has been deprecated in MySQL v8.0.20 and will be removed in the future. Since MySQL v8.0.19, aliases can be provided for the row to be inserted and also for each of its columns.

Using the new features, for MySQL v8.0.19 and newer use this query:

INSERT INTO data1 (id, date, quantity) AS new_row
SELECT id, date, quantity
FROM other_table
WHERE date = '2015-03-01'
AND id=123
ON DUPLICATE KEY UPDATE
quantity = IFNULL(data1.quantity, new_row.quantity)

The AS new_row alias provides a name for the row to be inserted; the name can be used instead of the deprecated VALUES() function to access the columns of the row to be inserted.

Check the documentation of INSERT ... ON DUPLICATE KEY UPDATE statement for details and examples.

Upvotes: 3

Shaeldon
Shaeldon

Reputation: 883

MySQL doesn't know to which quantity column you are referring since it is present in both data1 and other_table tables.

You have to use it like this: other_table.quantity

Upvotes: 0

Deepak
Deepak

Reputation: 47

Your query will change like this

INSERT IGNORE INTO data1 (id, date, quantity)
SELECT id, date, quantity
FROM other_table
WHERE date = '2015-03-01'
AND id=123
ON DUPLICATE KEY UPDATE 
data1.quantity = IF(other_table.quantity IS NULL,
VALUES(other_table.quantity), other_table.quantity)

Upvotes: 0

Related Questions