Updating a number of row with a single SQL query in Oracle DBMS

Question

I have a table A which has a structure like this:

ID   Name  City  zip

1    xxx   wer   134

2    yyy   qwe   234

3    zzz   ert   256

4    www   qwe   567

Now i would like to update multiple rows with one SQL query. For instance Query:

UPDATE A
   SET zip= '355'
   WHERE id= '2';

Will update one row, what if I also want to update row with ID 1 and 4 with just one query, where ZIP are different?

Answer 1

If you mean that you want to update multiple rows to the same zip with one query, you can use the following:

UPDATE A
   SET zip= '355'
   WHERE id in ('1','4');

However, if you mean that you want to update multiple rows to zip values this cannot be done without some logic sitting behind which id should get which zip. For example, if you wanted to just do a few different values you could use something like:

UPDATE A
   SET zip= decode(id,'1','100','4','400')
   WHERE id in ('1','4');

This sets the zip of any rows where the id is '1' to '100', any rows where id is '4' to '400' and so on. You can add as many arguments as you need to. If you want a default value for any not listed just add this to the end e.g. decode(id,'1','100','4','400','999') would set any that are not id '1' or '4' (but were not excluded in the where statement to '999').

If you have a lot of different values, then I would suggest creating a reference data table and doing a select from this table as a subquery within your update statement.