code formating

Wednesday, May 5, 2010

Why "If" if we can "Coalesce"

Today I wanted to update a row on an Oracle database table. The PL/SQL query was not difficult at all, however, there was a peculiarity. Some fields only needed to be updated if they where null. So, if these particular fields already had a value,we wanted to keep them, otherwise, update them.

The first solution to came to my mind was, reading the row and doing different updates depending on the values. Alas using "If" statements.
However it is possible to do everything in a relatively small PL/SQL update by using coalesce. This might be an example of how to use it.

Update table xxx set yyy=coalesce(yyy,new_value) where .....

No comments:

Post a Comment

Expose text from Emacs to another device via QR

Expose text from Emacs to another device via QR I am one of those who uses Emacs extensively. Particularly when it comes to keeping myself o...