CODEWITHSUNDEEP
mysqlsql-server
milo2011
milo2011

Reputation: 339

How to migrate SQL update query to MySQL?

I have a table called 'Books' with the following structure and records:

+--------+----------------------------+----------+
| BookID |          BookName          | Language |
+--------+----------------------------+----------+
|      1 | Le Comte de Monte-Cristo   | FR       |
|      1 | The Count of Monte Cristo  | EN       |
|      2 | Les Trois Mousquetaires    | FR       |
|      2 | The Three Musketeers       | EN       |
+--------+----------------------------+----------+

I want to overwrite the FR book name from EN based on the ID.

I have a SQL Server query that works, but when I try to run it on a PHPMyAdmin (MySQL) database, I receive an error:

You have an error in your SQL syntax

The MS SQL Server query:

UPDATE
    a
SET
    BookName = B.BookName
FROM
    Books a
    CROSS JOIN Books b
WHERE
    a.Language = 'FR' AND
    b.Language = 'EN' AND
    a.BookID   = b.BookID

Can someone, please, tell me where the problem is?

Thank you.

Upvotes: 2

Views: 1188

Answers (1)

jpw
jpw

Reputation: 44891

I didn't try it, but I'm guessing the statement you want is:

UPDATE Books A 
JOIN Books B ON A.BookID = B.BookID
SET A.BookName = B.BookName 
WHERE A.Language='FR' AND B.Language='EN'

A cross join makes a cartesian product, and in your example where you have a where clause that references each table, it turns in to an inner join

Upvotes: 1

Related Questions