Problem with where clause while updating database record

Question

I have a screen in ABAP that makes it possible to update a database row. It's sort of working: I can update the 'row' but the problem is, that it's updating EVERY row in the table and not the one specified in the where clause.

This is the code I'm using:

UPDATE zmotoren_jat SET:
prijs = zmotoren_jat-prijs,
naam = zmotoren_jat-naam
WHERE motorid = zmotoren_jat-motorid. "this line doesn't seem to work!

Any idea why this won't work? I'm sure that 'motorid' exists: I'm not getting an error and I'm using the same line, motorid = zmotoren_jat-motorid to delete a row, which does work.

score 4 · Accepted Answer · edited Jul 03 '19 at 19:28

Your problem seems to have to do with the colon (:) in the first line, and the comma (,) in the second line.

The colon introduces chained statements, and it could be seeing the first one with prijs as a separate statement, thereby updating all records in the first statement (because the WHERE clause then only applies to the second statement).

Take out the colon and remove the comma between the SET specifiers and try again.

See Esti answer for an explanation with some example code.

score 2 · Answer 2 · answered May 31 '11 at 08:07

Mydog has the correct answer.

Basically your syntax translates to the following:

UPDATE zmotoren_jat SET prijs = zmotoren_jat-prijs.
UPDATE zmotoren_jat SETnaam = zmotoren_jat-naam
 WHERE motorid = zmotoren_jat-motorid.

i.e it updates the price for every record in the table, and then updates the name for the specified ID. what you want is

UPDATE zmotoren_jat 
  SET prijs = zmotoren_jat-prijs
      naam  = zmotoren_jat-naam
WHERE motorid = zmotoren_jat-motorid.

I like the fact that you provide the corrected code sample. In that regard it was better than my answer. — mydoghasworms, May 31 '11 at 18:28

Problem with where clause while updating database record

2 Answers2