2

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.

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
milo2011
  • 339
  • 1
  • 9
  • 25
  • What is the exact error message? MySQL should be telling you more information about the error, such as the line number or the exact syntax error description. – Dai Mar 27 '16 at 00:24
  • Possible duplicate of [How to use JOIN in UPDATE query?](http://stackoverflow.com/questions/15209414/how-to-use-join-in-update-query) – jpw Mar 27 '16 at 00:25

1 Answers1

1

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

jpw
  • 44,361
  • 6
  • 66
  • 86