Query To update a table [message #613919] |
Tue, 13 May 2014 09:59 |
|
9390512774
Messages: 103 Registered: January 2011 Location: hyd
|
Senior Member |
|
|
I have a table XYZ. I have 30 columns in this table and have 3000million records. This table is a partition table and the partition is based on year and month (ex: 201405,201404,201403 ..etc..) and the partitioned column is col25.
Let us suppose assume that I have columns like col1 to col30. In that I want to update columns col1, col5, col8, col15, col21, col23, col27, col30.
Now my requirement is if any one of the above column has NULL value I want to put 0(ZERO) in that column otherwise left the value as it is.
I want a update query to update the NULL values in this table on multiple columns.
Experts Please provide me an update query which yields more performance.
THANKS IN ADVANCE FOR YOUR GREAT AND QUICK HELP.
|
|
|
|
|
|
|
|
|
Re: Query To update a table [message #613927 is a reply to message #613919] |
Tue, 13 May 2014 10:31 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
9390512774 wrote on Tue, 13 May 2014 20:29Now my requirement is if any one of the above column has NULL value I want to put 0(ZERO) in that column otherwise left the value as it is.
I want a update query to update the NULL values in this table on multiple columns
Those two statements seems to be vague. I thought it to be two different requirements which could be handled together using NVL2. If your requirement is just to update NULL with zero then NVL should suffice, as suggested by CM.
It's better to post a test case.
|
|
|
Re: Query To update a table [message #613929 is a reply to message #613926] |
Tue, 13 May 2014 10:35 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
9390512774 wrote on Tue, 13 May 2014 16:30is there any posibility to update multiple columns with single UPDATE statment.
No other possibility has been suggested, just list all the relevant columns in the set clause.
|
|
|
Re: Query To update a table [message #613930 is a reply to message #613926] |
Tue, 13 May 2014 10:36 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
9390512774 wrote on Tue, 13 May 2014 21:00is there any posibility to update multiple columns with single UPDATE statment.
Please read about UPDATE clause. It handles multiple columns update. What have you tried so far?
Quote:
I want the UPDATE statment with performance driven.
What performance issue with a plain DML, unless you post it.
[Updated on: Tue, 13 May 2014 10:40] Report message to a moderator
|
|
|