If, in addition, you use the column aliases m , n , and p , you can omit the row alias in the assignment clause and write the same statement like this: 

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p) ON DUPLICATE KEY UPDATE c = m+n; 

When using column aliases in this fashion, you must still use a row alias following the VALUES  clause, even if you do not make direct use of it in the assignment clause. 

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; 

You can also use row and column aliases with a SET  clause, as mentioned previously. Employing SET instead of VALUES  in the two INSERT ... ON DUPLICATE KEY UPDATE  statements just shown can be done as shown here: 

INSERT INTO t1 SET a=1,b=2,c=3 AS new ON DUPLICATE KEY UPDATE c = new.a+new.b; INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p) ON DUPLICATE KEY UPDATE c = m+n; 

The row alias must not be the same as the name of the table. If column aliases are not used, or if they are the same as the column names, they must be distinguished using the row alias in the ON DUPLICATE KEY UPDATE  clause. Column aliases must be unique with regard to the row alias to which they apply (that is, no column aliases referring to columns of the same row may be the same). 

For INSERT ... SELECT  statements, these rules apply regarding acceptable forms of SELECT  query expressions that you can refer to in an ON DUPLICATE KEY UPDATE  clause: 

•References to columns from queries on a single table, which may be a derived table. 

•References to columns from queries on a join over multiple tables. 

•References to columns from DISTINCT  queries. 

•References to columns in other tables, as long as the SELECT  does not use GROUP BY . One side effect is that you must qualify references to nonunique column names. 

References to columns from a UNION  are not supported. To work around this restriction, rewrite the UNION as a derived table so that its rows can be treated as a single-table result set. For example, this statement produces an error: 

INSERT INTO t1 (a, b) SELECT c, d FROM t2 UNION SELECT e, f FROM t3 ON DUPLICATE KEY UPDATE b = b + c; 

Instead, use an equivalent statement that rewrites the UNION  as a derived table: 

