1

I have two/three state variables stored in DB-table fields and I wonder what's the best way to toggle them (from 1 to 0 and vice versa) and return back their new value. Running two queries seems too much to me. Is there another, better way?

Here is my query now (i haven't tested it yet):

UPDATE MyTable qrus
SET qrus.favorite=(CASE WHEN (qrus.favorite=0) THEN 1 ELSE 0 END)
WHERE <sth>;

I am using OracleDataClient.

I don't know if I could insert an additional select after the update but in the same CommandText. I will try this. But isn't there a better way?

schurik
  • 7,798
  • 2
  • 23
  • 29
user2173353
  • 4,316
  • 4
  • 47
  • 79

4 Answers4

1

I don't think what you want is possible. If you desperately want your application to only make one call, you could use a stored function to update and then retrieve (or the other way around, retrieve and then update, doesn't matter). But that's still 2 statements, it's just that the application only makes 1 call.

1

You can use a sub-query to pull the inverted value and join back to the parent query. Then use a RETURNING clause.

UPDATE MyTable qrus
SET qrus.favorite = ( 
  SELECT CASE WHEN b.favorite = 0 THEN 1 ELSE 0 END
  FROM MyTable b WHERE grus.PK_FIELDS =b.PK_FIELDS
)
WHERE grus.PK_FIELDS = :pkFields
RETURNING grus.favorite INTO :favorite;

Just confirmed this on Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

pcnate
  • 1,694
  • 1
  • 18
  • 34
0

For more info I posted a question on the site of Oracle. https://forums.oracle.com/message/11234565

user2173353
  • 4,316
  • 4
  • 47
  • 79
0

Update TABLENAME set status = decode(status,1,0,1)

Mahesh
  • 1