Re: UPDATE SET of multiple columns

"DBA9999" <cdavis10717_at_comcast.net> wrote in message news:1144951804.217665.201660_at_g10g2000cwb.googlegroups.com...
:I have a table with several columns that may have null values.
:
: I want to set the columns to 0 if they are null.
:
: An UPDATE statement like this below gives me a syntax error, but how
: could all the columns be set to 0 in one update statement?
:
: Thanks.
:
: C
:
:
: update DISK_STATS_TBL
: set DISKBSIZE_STAT = 0 where DISKBSIZE_STAT IS NULL,
: DISKBUSY_STAT = 0 where DISKBUSY_STAT IS NULL,
: DISKREAD_STAT = 0 where DISKREAD_STAT IS NULL,
: DISKWRITE_STAT = 0 here DISKWRITE_STAT IS NULL,
: DISKXFER_STAT = 0 where DISKXFER_STAT IS NULL,
: DISKSERV_STAT = 0 where DISKSERV_STAT IS NULL
: ;
: set DISKBSIZE_STAT = 0 where DISKBSIZE_STAT IS NULL,
: *
: ERROR at line 2:
: ORA-00933: SQL command not properly ended
:

you can have only one where clause per UPDATE

you need to use the OR keyword between multiple WHERE predicates and use the NVL or CASE in the SET clause for each column

++ mcs Received on Thu Apr 13 2006 - 13:27:47 CDT