How to join table to value instead of another table in mySQL

Question

In SQL server we can update set join like this,

Update table1 set column = n.newvalue from table1 join (values('value1', 'value2')) N (oldvalue, newvalue) on table1.column = oldvalue

I try to write something like this is mySQL, different from SQL server mySQL use something like this

update table join table2 on table1.column = table2.column set table1.column = table2.column

So I tried to write it like this

update table1 join (values('value1','value2')) N (oldvalue, newvalue) on table.column = n.oldvalue
set table.column = n.newcolumn

but I always get the error check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES, so is there something I can use other than values, or is there another method of writing this query

You will need a subquery with its own `SELECT` as shown in https://stackoverflow.com/a/985299/2055998 — PM 77-1, Dec 27 '19 at 02:27

Tim Biegeleisen · Answer 1 · 2019-12-27T02:46:57.227

1

MySQL does not support the table value constructor which is available in SQL Server. The closest alternative, assuming you are using MySQL 8+, might be to use a CTE:

WITH N (oldvalue, newvalue) AS (
    SELECT 'value1', 'value2'
)

UPDATE table1 t1
INNER JOIN N n
    ON t1.column = n.oldvalue
SET
    t1.column = n.newcolumn;

This approach still requires an explicit reference to SELECT (table value constructor does not), but it at least lets you keep the literal value in a logically separate segment of code.

On earlier versions of MySQL, you would have to inline the above CTE as subquery, giving you:

UPDATE table1 t1
INNER JOIN
(
    SELECT 'value1' AS oldvalue, 'value2' AS newvalue
) n
    ON t1.column = n.oldvalue
SET
    t1.column = n.newcolumn;

edited Dec 27 '19 at 02:46

answered Dec 27 '19 at 02:35

Tim Biegeleisen

502,043
27
286
360

thanks for the feedback unfortunately i'm using mySql 5.7.28 – Misael Indra Wijaya Dec 27 '19 at 02:45
1

@MisaelIndraWijaya Then you'll have to use a subquery...or maybe just create a table which contains these values. – Tim Biegeleisen Dec 27 '19 at 02:47
i guess subquery is the only way to go, thanks for your help – Misael Indra Wijaya Dec 27 '19 at 02:50

score 1 · Answer 2 · answered Dec 27 '19 at 02:52

1

I'm wondering why you don't just write the query like this:

Update table1
    set column = 'value2'
    where column = 'value1';

This will work in any database.

answered Dec 27 '19 at 02:52

Gordon Linoff

1,242,037
58
646
786

well i want to update each different row, with specific value, so i don't think repeating update syntax would be very effective – Misael Indra Wijaya Dec 27 '19 at 02:57

How to join table to value instead of another table in mySQL

2 Answers2