CODEWITHSUNDEEP
mysqlsql
Misael Indra Wijaya
Misael Indra Wijaya

Reputation: 23

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

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

Upvotes: 1

Views: 157

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

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.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522234

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;

Upvotes: 1

Related Questions