0

after a few time to find the error, i would ask u if anyone has some idea?

UPDATE benutzer_attributes 
SET benutzer_attributes.z_overlast_week = benutzer_attributes.z_last_week
FROM benutzer_attributes 
    INNER JOIN benutzer_groups ON benutzer_groups.ID = benutzer_attributes.ID
WHERE benutzer_groups.LEVEL = '4';

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM benutzer_attributes INNER JOIN benutzer_groups ON benutzer_groups.ID =' at line 3

Thanks und greetings!

jarlh
  • 42,561
  • 8
  • 45
  • 63
John Skyline
  • 1
  • I'd skip the JOIN and instead have an EXISTS/IN condition in the WHERE clause. (But I don't know MySQL very well.) – jarlh Mar 02 '22 at 19:59
  • SET belongs after all the table references, and there is no FROM clause, it's just `UPDATE sometable [joins clauses] SET ... WHERE ...` – ysth Mar 02 '22 at 20:02

1 Answers1

0

MySQL does not use FROM in UPDATE. See UPDATE Statement, "Multiple-table syntax".

UPDATE benutzer_attributes 
INNER JOIN benutzer_groups ON benutzer_groups.ID = benutzer_attributes.ID
SET benutzer_attributes.z_overlast_week = benutzer_attributes.z_last_week
WHERE benutzer_groups.LEVEL = '4';
Akina
  • 39,301
  • 5
  • 14
  • 25
  • Is this, MySQL-wise, better than an EXISTS/IN condition in the WHERE clause? – jarlh Mar 02 '22 at 20:07
  • Thanks for the answer. Unfortunately, the no datas that match the benutzer_groups.LEVEL = '4'; are considered in the query. – John Skyline Mar 02 '22 at 20:10
  • @jarlh This is syntax only. Not inner algorithm. I.e. no difference from the execution looking point. But from the logic point this syntax is more clear (for me at least) - when the same table is listed in UPDATE and in FROM then this is looks like possible recursion (updated record may, after the update, match WHERE condition that it did not matched before the update). – Akina Mar 02 '22 at 21:21
  • @Akina, thank you for the explanation. (However, I'd do the opposite, write portable SQL - as long as there's no disadvantage.) – jarlh Mar 02 '22 at 21:24
  • @jarlh Each DBMS uses its own syntax - this is a fact. Nothing can be done about it, you can only put up with it and use what is available. – Akina Mar 02 '22 at 21:26
  • 1
    I'd expect `UPDATE benutzer_attributes SET benutzer_attributes.z_overlast_week = benutzer_attributes.z_last_week WHERE EXISTS (SELECT 1 FROM benutzer_groups WHERE benutzer_groups.ID = benutzer_attributes.ID AND benutzer_groups.LEVEL = '4')` to be pretty portable. – jarlh Mar 02 '22 at 21:35
  • 1
    @jarlh yes, unless the same table is referenced in the subquery – ysth Mar 02 '22 at 22:02
  • @jarlh In this particular case your query ir really more useful. I have told about general situation. In most cases another copy of the table to be updated should be a source of the subquery in WHERE - but this is not allowed. Therefore, unless there are serious problems with query performance, I prefer using JOIN. Solely for the sake of uniformity. – Akina Mar 03 '22 at 04:52