CODEWITHSUNDEEP
mysqlsql
eevaa
eevaa

Reputation: 1457

MySQL - Update selected data

I have sort of a translation database. One table with words and their IDs in language A (langA), one with words and IDs in language B (langB) and one that connects the words in both languages (translation), like ID-idA-idB.

In order to display a translation by translation ID I use a joined table:

SELECT * 
FROM langA, langB, translation
WHERE translation.id = 5 
    AND langA.id = translation.idA 
    AND langB.id = translation.idB

The last two lines are because one word may have more than one translation.

Now I'd like to update the information for a certain translation by its ID. Seems it would be easiest to update what I just selected.

I found a syntax like this

UPDATE langA
SET langA.word = 'newword'   
FROM langA, langB, translation
WHERE translation.id = 5 
    AND langA.id = translation.idA
    AND langB.id = translation.idB

However, this gives me an error "near 'langA, langB, translation WHERE translation.id = ...'"

What am I doing wrong?

Upvotes: 2

Views: 170

Answers (1)

John Woo
John Woo

Reputation: 263703

The one you showed is for TSQL. Here's the syntax for MySQL on multi-table update.

UPDATE langA
        INNER JOIN translation
            ON langA.id = translation.idA
        INNER JOIN langB
            ON langB.id = translation.idB
SET langA.word = 'newword'  
WHERE translation.id = 5

Upvotes: 1

Related Questions