[SOLVED] MariaDB UPDATE with two INNERJOINs and WHERE

MariaDB UPDATE with two INNERJOINs and WHERE

We want to perform an UPDATE with two INNER JOINs and cannot explain why this does not work. Can you help us?

UPDATE problems
SET problems.PROBLEM = CONCAT ('XX', problems.PROBLEM)
FROM problems
 INNER JOIN
  icdtencodes
  ON
  problems.REF_ICDTENCODE = icdtencodes.ICD_ROWID

  INNER JOIN
  solvingmethods
  ON
  icdcodes.REF_SOLVINGMETHOD = solvingmethods.ICD.ROWID
WHERE
 (solvingmethods.SOLVINGMETHOD LIKE 'R' OR solvingmethods.SOLVINGMETHOD LIKE 'K'
 AND (d.ICD_FIX_REVISION LIKE '')

We are getting the Errorcode: 1064 - You habe an Error in SQL syntax on line ... The problem should be on the FROM

We switched the INNER JOIN with the SET but got the same problem.

Solution

The correct syntax would be:

UPDATE problems INNER JOIN
  icdtencodes
  ON
  problems.REF_ICDTENCODE = icdtencodes.ICD_ROWID

  INNER JOIN
  solvingmethods
  ON
  icdcodes.REF_SOLVINGMETHOD = solvingmethods.ICD.ROWID

SET problems.PROBLEM = CONCAT ('XX', problems.PROBLEM)
 WHERE
 (solvingmethods.SOLVINGMETHOD LIKE 'R' OR solvingmethods.SOLVINGMETHOD LIKE 'K'
 AND (d.ICD_FIX_REVISION LIKE '')

In your query, you have mentioned JOIN after SET