0

I am trying to update a column with a calculated column in a inner join. The logic is simple but I am struggling with the syntax (this is just a dummy SQL, to explain what I am trying to accomplish - it does not run)

UPDATE t1
SET t1.BodyText = t2.final
from Questions as t1 
INNER JOIN translations as t2 
on t2.QuestionId=t1.QuestionID
CONCAT(t1.BodyText,t2.QuestionBodyText) as final

The task is simple, concat a question with its translation. I found some questions related to this issue on stackoverflow, but they where no help, maybe because they were discussing SQL Server.

Similar: Update a table using JOIN in SQL Server?

I tried that:

UPDATE Questions t1
JOIN translations t2
on t1.QuestionID=t2.QuestionId
SET t1.BodyText = CONCAT(t1.BodyText,t2.QuestionBodyText)

But it does not have any effect on the database.

This is an equivalent SELECT that works:

SELECT CONCAT(t1.BodyText,t2.QuestionBodyText)  FROM Questions t1
JOIN translations t2
on t1.QuestionID=t2.QuestionId

Update, when I used this update query on phpmyadmin it worked, on workbench it did not..

Community
  • 1
  • 1
Victor
  • 3,520
  • 3
  • 38
  • 58

1 Answers1

3

Ah, your syntax is out of whack.

Update Questions t1
join translations t2
on t2.QuestionID = t1.QuestionID
set t1.BodyText = concat(t1.bodytext,t2.questionbodytext)
Andrew
  • 8,445
  • 3
  • 28
  • 46
  • Thanks. I corrected on the way you said, but it runs without any changes. I will investigate more, it might be another problem. – Victor Mar 26 '15 at 21:26
  • no changes. which is weird because if I convert this query into a select it works as expected. – Victor Mar 26 '15 at 21:29
  • Update, when I used this query on phpmyadmin it worked, on workbench it did not.. – Victor Mar 26 '15 at 21:51
  • 1
    SET SQL_SAFE_UPDATES=0; you have to run that before you run the update in mysql workbench – BK435 Mar 26 '15 at 21:56