Oracle SQL: UPDATE, but throw 'error' if condition not met

Question

Sorry if this is a dupe -- I searched for similar questions, but they all seemed to be along the lines of conditional updates where you can use CASE statements to set a column to one of several values.

I've got a SQL UPDATE statement, but there are two reasons why I would want it to NOT update:

the item isn't found (causing rowsUpdated to be 0)
if a condition is met

I'd like to differentiate between these two states somehow, which is why I can't just put the condition in the WHERE clause.

It currently looks something like this:

UPDATE MY_TABLE t 
   SET t.TITLE = :TITLE,
       t.TYPE = :TYPE,
       t.STATE = :STATE,
       t.UPDTIME = :UPDTIME
 WHERE t.ITEM_ID = :ITEM_ID 
   AND exists (SELECT t.ITEM_ID
                FROM MY_TABLE t, USERINFO ui
               WHERE ui.USERID = t.USERID
                 AND ui.USERNAME = :USERNAME
                 AND t.ITEM_ID = :ITEM_ID);

That will return 1 if it finds (and updates) an item in MY_TABLE that matches the given USERNAME and ITEM_ID, or 0 if it doesn't.

I'd like to add something so that it doesn't update if t.STATE = 'READ_ONLY', but so that it does something to differentiate that condition from the "that item doesn't exist" condition. Maybe throw an error of some sort?

I could run a SELECT statement before my update to make sure that item's state isn't READ_ONLY, but that seems wasteful. Is there a better way?

You could add a dummy calculation like t.UPDTIME = CASE WHEN t.STATE = 'READ_ONLY' THEN 1/0 ELSE :UPDTIME END. The 1/0 should throw a division by zero you can catch. — dnoeth
– dnoeth, Commented Feb 11, 2016 at 22:33
That's clever, but a bit hacky, wouldn't you say? I like where your head is :) I was hoping for some sort of custom error -- but I'm not even sure that's possible in SQL. That way, we'd avoid ignoring a legit divide by zero error, as Felipe said. Not that that's likely to happen with our current schema setup, but who knows what the future holds? — Disgruntor
– Disgruntor, Commented Feb 12, 2016 at 19:27

Felipe Moreno · Accepted Answer · 2016-02-12 06:17:44Z

1

I don't think it's wasteful a previous SQL. It would be confusing throwing an error just to catch a condition (what if your real update threw the same divided by zero error?). You could select for update, and update it by rowid afterwards to speed things up if your are using a heap table:

SELECT t.ITEM_ID, t.condition, t.rowid
FROM MY_TABLE t, USERINFO ui
WHERE ui.USERID = t.USERID
AND ui.USERNAME = :USERNAME
AND t.ITEM_ID = :ITEM_ID for update;

If your condition is met, you just update the table by rowid

update MY_TABLE
set ...
where rowid = :rowid

answered Feb 12, 2016 at 6:17

Felipe Moreno

5082 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL: UPDATE, but throw 'error' if condition not met

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related