SQL UPDATE with multiple WHERE (relations) conditions

Question

I would like to know if it is possible to perform such UPDATE in oracle SQL database :

UPDATE mark
        SET
        mark=
        CASE
        WHEN mark.val<= 5 
            THEN val*1.1
        ELSE val END
        WHERE mark.id_classes = classes.id_classes 
            AND classes.ID_subject = subject.ID_subject
            AND subject.ID_subject = 5;

SQL developer returns error in this part :

WHERE mark.id_classes = classes.id_classes 
            AND classes.ID_subject = subject.ID_subject
            AND subject.ID_subject = 5;

So I guess that it is not possible to make such a complex condition, is it any other way to do that then?

Might be silly to try more SELECT like condition but on the other hand I don't see the reason why it is not working.

Answer 1

You can use a subquery:

UPDATE mark
    SET mark = val * 1.1
    WHERE mark.val <= 5 AND
          EXISTS (SELECT 1
                  FROM classes c JOIN
                       subjects s
                       ON c.ID_subject = s.ID_subject
                  WHERE mark.id_classes = c.id_classes AND                       
                        s.ID_subject = 5
                 );

Notice that I moved the CASE condition to the WHERE clause so only the rows that need to be updated are updated.

Answer 2

You can't reference another two tables (CLASSES and SUBJECT) just like that, out of nowhere. Here's code which shows how you might have done that:

update mark m set
  m.mark = (select case when m.val <= 5 then m.val * 1.1
                        else m.val
                   end
            from classes c join subject s on c.id_subject = s.id_subject
            where c.id_classes = m.id_classes
              and s.id_subject = 5
           )
where ... 

As you didn't use table aliases within CASE, I don't know which table the VAL column belongs to (so I presumed it is MARK).

Also, UPDATE itself might need the WHERE clause which would restrict number of rows to be updated.