Question 112720 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – INSERT…SELECT…ON DUPLICATE KEY UPDATE without using deprecated VALUES() function

TL;DR (i.e. asking the question first):

Is there any way to write an INSERT INTO...SELECT FROM...GROUP BY...ON DUPLICATE KEY UPDATE statement using row alias(es) in the ON DUPLICATE KEY UPDATE clause instead of the col1 = VALUES(col1) syntax that has been deprecated and will be removed from future MySQL releases?

My searches of SO relating to this issue tend to all suggest using the deprecated VALUES() function, which is why I believe that my question is not a duplicate.

BACKGROUND (i.e. more info on how to reproduce the issue)

I have a table that comprises grouped records from another table. For simplicity in describing this issue, I’ve created two sample tables purely to illustrate:

items:

items table

item_groups (below) was populated using the following SQL:

insert into item_groups (item_type,quantity) (select item_type, count(*) from items group by item_type order by item_type)

It also has a unique index on item_type:

item_groups table

Now, let’s say that I add two more items to the items table, one with an item_type of 4 and one with a new item_type of 5. The quantity of item_type 4 in item_groups should be updated to 3 and a new row inserted for the item_type of 5 with quantity of 1.

Using the same INSERT statement I used above to initially populate the item_groups table, I now get an error, which is expected because of a duplicate key (4 of the 5 item_types currently in the items table are duplicates of the item_types that currently exist in the item_groups table):

insert error 1

Zero updates or inserts were completed due to this error. To remedy this, we would have historically used the ON DUPLICATE KEY UPDATE (occasionally abbreviated to ODKU below) clause like so including the VALUES() function:

insert into item_groups (item_type,quantity) (select item_type, count(*) from items group by item_type order by item_type) ON DUPLICATE KEY UPDATE quantity = VALUES(quantity);

The above INSERT...ON DUPLICATE KEY UPDATE statement with VALUES() DOES work (currently)…

item_groups after first update

However, I am also greeted with the following warning:

‘VALUES function’ is deprecated and will be removed in a future
release. Please use an alias (INSERT INTO … VALUES (…) AS alias)
and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with
alias.col instead

Now, I know how to write a simple INSERT…ODKU statement to be future-proof against the warning above (generically):

INSERT INTO `my_table` (col1,col2,col3) VALUES (1,2,3) AS new ON DUPLICATE KEY UPDATE col1 = new.col1, col2 = new.col2, col3 = new.col3

But let’s insert more items into my items table and then use the above syntax for my more complicated INSERT…SELECT…ODKU statement into item_groups:

insert into item_groups (item_type,quantity) (select item_type, count(*) from items group by item_type order by item_type) AS new ON DUPLICATE KEY UPDATE quantity = new.quantity;

I get this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near ‘AS new ON DUPLICATE KEY UPDATE quantity =
new.quantity’ at line 1

Adding "VALUES" prior to my SELECT subquery, like so…

insert into item_groups (item_type,quantity) VALUES (select item_type, count(*) from items group by item_type order by item_type) AS new ON DUPLICATE KEY UPDATE quantity = new.quantity;

I now get a new syntax error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near ‘select item_type, count(*) from items group by
item_type order by item_type) AS ‘ at line 1

Finally, at my wit’s end, I try adding another set of parentheses around the SELECT sub-query…

insert into item_groups (item_type,quantity) VALUES ((select item_type, count(*) from items group by item_type order by item_type)) AS new ON DUPLICATE KEY UPDATE quantity = new.quantity;

…and I still get an error:

ERROR 1136 (21S01): Column count doesn’t match value count at row 1

This appears to be "progress" as I’m no longer getting syntax errors; however, I don’t understand why the column count doesn’t match the value count. My SELECT subquery pulls in 2 values for each row and the INSERT attempts to insert those into 2 columns for each row. So it would seem to me that 2 values -> 2 columns should not be an issue; yet it is.

CONCLUSION

I’m frankly not even sure what else to try, and I’m about ready to give up doing it this way and just write a simple SELECT, store those retrieved values in variables, and then use a simple INSERT to insert those values (wrapping everything in a transaction). However, if there is a way to do what I’m trying to do in one statement, I would appreciate anyone who can help me to do this.

Tags:

2

Answers


  1. 0

    You need a version mysql that is newer

    8.0.30 and 8.0,31 this works

    The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.20, and is subject to removal in a future version of MySQL. Instead, use row and column aliases, as described in the next few paragraphs of this section.

    so it shpuld work with 8.0.20 too

    Besides security risks, with very update come new functions and old bugs are fixed.

    Deploying for two Versions is bad, as you need more and more code to support more database version or to simulate functions you need, that you open your code to more and more bugs and insecurities.

    So make a cut and use the latest Version

    CREATE TABLE `my_table` (col1 int unique,col2 int ,col3  int)

    INSERT INTO `my_table` (col1,col2,col3) VALUES (1,2,3) AS new 
  ON DUPLICATE KEY UPDATE col1 = new.col1, col2 = new.col2, col3 = new.col3

    SELECT * FROM `my_table`
    col1 col2 col3
    1 2 3

    fiddle

    Login or Signup to reply.
  2. 0

    From MySQL docs

    Beginning with MySQL 8.0.20, an INSERT … SELECT … ON DUPLICATE KEY
    UPDATE statement that uses VALUES() in the UPDATE clause, like this
    one, throws a warning:

    INSERT INTO t1 SELECT c, c+d FROM t2 ON DUPLICATE KEY UPDATE b =
    VALUES(b); You can eliminate such warnings by using a subquery
    instead, like this:

    INSERT INTO t1 SELECT * FROM (SELECT c, c+d AS e FROM t2) AS dt ON
    DUPLICATE KEY UPDATE b = e;

    In simple words you could use a subquery as follows:

    insert into item_groups (item_type,
                         quantity) 
select * from ( select item_type , count(*) as new_quantity  from items group by item_type ) as tbl 
 ON DUPLICATE KEY UPDATE quantity = new_quantity;

    https://dbfiddle.uk/HoMLKMfd

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search