473,890 Members | 1,429 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Multiple column updates in a single query

DB2 V8.2 on AIX

I am looking for an efficient way to update several columns in a table
that will have a default change. The problem is the table is large
(million records) and there are 1 to 4 columns that might need to be
changed per record. I wanted to avoid looping through the table 4
times in order to change them.

Here is an example. Basically, the four columns in question were
originally defaulted to NULL. Due to changes elsewhere it can no
longer be NULL so it will be changed to blanks. Here is what the
table would look like prior to changing the default:

table1
col1 bigint
col2 char(8) default null
col3 char(8) default null
col4 char(8) default null
col5 char(8) default null
col6 smallint default 0

So now there are records in the table where some of the values in col2
thru col5 are still NULL and some contain values.

I want to parse the table and change anything that is null in these
four columns to blanks (' '). I am hoping to do with without
having to loop through it four times because I dont know if all
columns are null or maybe only one or two.

Anyone have a good query to do this?

Much Appreciated.

Apr 12 '07 #1
8 12023
Don't know if it's the most efficient way, but you can use a case
expression that returns the blanks when the column is null, else the
column value itself, then use that to unconditionally update all rows.
Something like (not tested):

update table1 set c2=(case when c2 is null then ' ' else c2 end),
c3=(case when c3 is null then ' ' else c3 end), c4=(case when c4 is
null then ' ' else c4 end), c5=(case when c5 is null then ' '
else c5 end)

You might want to chunk up the update if the table is huge and your
active log space is limited. Hope this helps.

Regards,
Miro

On Apr 11, 7:57 pm, "shorti" <lbrya...@juno. comwrote:
DB2 V8.2 on AIX

I am looking for an efficient way to update several columns in a table
that will have a default change. The problem is the table is large
(million records) and there are 1 to 4 columns that might need to be
changed per record. I wanted to avoid looping through the table 4
times in order to change them.

Here is an example. Basically, the four columns in question were
originally defaulted to NULL. Due to changes elsewhere it can no
longer be NULL so it will be changed to blanks. Here is what the
table would look like prior to changing the default:

table1
col1 bigint
col2 char(8) default null
col3 char(8) default null
col4 char(8) default null
col5 char(8) default null
col6 smallint default 0

So now there are records in the table where some of the values in col2
thru col5 are still NULL and some contain values.

I want to parse the table and change anything that is null in these
four columns to blanks (' '). I am hoping to do with without
having to loop through it four times because I dont know if all
columns are null or maybe only one or two.

Anyone have a good query to do this?

Much Appreciated.

Apr 12 '07 #2
shorti wrote:
DB2 V8.2 on AIX

I am looking for an efficient way to update several columns in a table
that will have a default change. The problem is the table is large
(million records) and there are 1 to 4 columns that might need to be
changed per record. I wanted to avoid looping through the table 4
times in order to change them.

Here is an example. Basically, the four columns in question were
originally defaulted to NULL. Due to changes elsewhere it can no
longer be NULL so it will be changed to blanks. Here is what the
table would look like prior to changing the default:

table1
col1 bigint
col2 char(8) default null
col3 char(8) default null
col4 char(8) default null
col5 char(8) default null
col6 smallint default 0

So now there are records in the table where some of the values in col2
thru col5 are still NULL and some contain values.

I want to parse the table and change anything that is null in these
four columns to blanks (' '). I am hoping to do with without
having to loop through it four times because I dont know if all
columns are null or maybe only one or two.

Anyone have a good query to do this?
update T
set (col2, col3, ...) = (coalesce(col2, ' '), coalesce(col3, '
'), ...)

/Lennart
Apr 12 '07 #3
On 12 Apr, 00:57, "shorti" <lbrya...@juno. comwrote:
DB2 V8.2 on AIX

I am looking for an efficient way to update several columns in a table
that will have a default change. The problem is the table is large
(million records) and there are 1 to 4 columns that might need to be
changed per record. I wanted to avoid looping through the table 4
times in order to change them.

Here is an example. Basically, the four columns in question were
originally defaulted to NULL. Due to changes elsewhere it can no
longer be NULL so it will be changed to blanks. Here is what the
table would look like prior to changing the default:

table1
col1 bigint
col2 char(8) default null
col3 char(8) default null
col4 char(8) default null
col5 char(8) default null
col6 smallint default 0

So now there are records in the table where some of the values in col2
thru col5 are still NULL and some contain values.

I want to parse the table and change anything that is null in these
four columns to blanks (' '). I am hoping to do with without
having to loop through it four times because I dont know if all
columns are null or maybe only one or two.

Anyone have a good query to do this?

Much Appreciated.
Could you not just do a export (with a case statement for the relevant
4 columns) and then an import from <file>.del of del commitcount 10000
insert_update into <table>.
It might be even quicker, if you were to truncate the table after
you've done the export and then do a load...nonrecov erable.

Apr 12 '07 #4
Don't forget to check the space consequences of changing the columns
from null to blanks. You'll be adding 8-32 bytes for each row containing
null columns. If you don't have enough free space in the existing pages,
rows will physically move during this update. This could have
significant performance consequences for existing SQL.

Phil Sherman
shorti wrote:
DB2 V8.2 on AIX

I am looking for an efficient way to update several columns in a table
that will have a default change. The problem is the table is large
(million records) and there are 1 to 4 columns that might need to be
changed per record. I wanted to avoid looping through the table 4
times in order to change them.

Here is an example. Basically, the four columns in question were
originally defaulted to NULL. Due to changes elsewhere it can no
longer be NULL so it will be changed to blanks. Here is what the
table would look like prior to changing the default:

table1
col1 bigint
col2 char(8) default null
col3 char(8) default null
col4 char(8) default null
col5 char(8) default null
col6 smallint default 0

So now there are records in the table where some of the values in col2
thru col5 are still NULL and some contain values.

I want to parse the table and change anything that is null in these
four columns to blanks (' '). I am hoping to do with without
having to loop through it four times because I dont know if all
columns are null or maybe only one or two.

Anyone have a good query to do this?

Much Appreciated.
Apr 12 '07 #5
On Apr 12, 6:50 am, Phil Sherman <psher...@ameri tech.netwrote:
Don't forget to check the space consequences of changing the columns
from null to blanks. You'll be adding 8-32 bytes for each row containing
null columns. If you don't have enough free space in the existing pages,
rows will physically move during this update. This could have
significant performance consequences for existing SQL.

Phil Sherman
Can I just do a reorg after all the records are updated to prevent the
performance problem?

I am leaning towards doing the EXPORT and LOAD of the 4 columns since
I agree this would be the most effecient. I dont expect anyone to be
manipulating the table while this is being done but do I need to
"LOCK" the table? I noticed the LOAD does it's own lock so will this
interfere? I am just concerned with the time between the EXPORT and
the LOAD.

The other queries are great and I will add them to my list for future
use.

(forgive me if this is a duplicate response...my last reply did not
seem to come through after 30+ minutes).

Apr 12 '07 #6
If you will have to reorg after processing the entire table, another
option is to do a SELECT of the entire table, constructing the output as
csv (comma separated variable) data. The SELECT can, using COALESCE,
replace any nulls with blanks. Once the data has been unloaded, change
the table definition from nullable to NOT NULL WITH DEFAULT and use the
load utility to rebuild the table. This will, however involve a lot more
DBA work. It'll also allow existing code to continue running and have
new data rows have appropriate defaults.

Phil Sherman
shorti wrote:
On Apr 12, 6:50 am, Phil Sherman <psher...@ameri tech.netwrote:
>Don't forget to check the space consequences of changing the columns
from null to blanks. You'll be adding 8-32 bytes for each row containing
null columns. If you don't have enough free space in the existing pages,
rows will physically move during this update. This could have
significant performance consequences for existing SQL.

Phil Sherman

Can I just do a reorg after all the records are updated to prevent the
performance problem?

I am leaning towards doing the EXPORT and LOAD of the 4 columns since
I agree this would be the most effecient. I dont expect anyone to be
manipulating the table while this is being done but do I need to
"LOCK" the table? I noticed the LOAD does it's own lock so will this
interfere? I am just concerned with the time between the EXPORT and
the LOAD.

The other queries are great and I will add them to my list for future
use.

(forgive me if this is a duplicate response...my last reply did not
seem to come through after 30+ minutes).
Apr 13 '07 #7
On Apr 13, 8:33 am, Phil Sherman <psher...@ameri tech.netwrote:
If you will have to reorg after processing the entire table, another
option is to do a SELECT of the entire table, constructing the output as
csv (comma separated variable) data.
You want to be VERY careful using csv files; most especially you want
to make sure there aren't any commas in your data, or you will not be
able to reload the data correctly. Also, I'm not sure what non-US
ASCII data would do - if your data is like that, you need to read the
documentation very carefully to make sure you know the limitations of
the csv (called DEL format) loading utility.

I'd be more inclined to recomment the ASC format (fixed width) or IXF
(internal) - the first requires quite a bit more setup, though.
However, both of these should successfully reload all of the data
correctly - no matter what the data is.

-Chris

Apr 16 '07 #8
Ian
ChrisC wrote:
On Apr 13, 8:33 am, Phil Sherman <psher...@ameri tech.netwrote:
>If you will have to reorg after processing the entire table, another
option is to do a SELECT of the entire table, constructing the output as
csv (comma separated variable) data.

You want to be VERY careful using csv files; most especially you want
to make sure there aren't any commas in your data, or you will not be
able to reload the data correctly. Also, I'm not sure what non-US
ASCII data would do - if your data is like that, you need to read the
documentation very carefully to make sure you know the limitations of
the csv (called DEL format) loading utility.
DB2 is very good with its delimited format.

By default the DEL format uses both character AND column delimiters.
The default character delimiter is a double-quote ("), and the
default column delimiter is a comma (,).

DB2 will handle column delimiters as long as the character delimiters
exist.

It will even handle embedded newlines (provided you use the
delprioritychar filetype-modifier).
I'd be more inclined to recomment the ASC format (fixed width) or IXF
(internal) - the first requires quite a bit more setup, though.
However, both of these should successfully reload all of the data
correctly - no matter what the data is.
You can't export data in fixed-width format from DB2 (on
Linux/Unix/Windows)

Apr 18 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
10008
by: Steven An | last post by:
Howdy, I need to write an update query with multiple aggregate functions. Here is an example: UPDATE t SET t.a = ( select avg(f.q) from dbo.foo f where f.p = t.y ), t.b = ( select sum(f.q) from dbo.foo f where f.p = t.y ) FROM dbo.test t
1
3199
by: Gregory.Spencer | last post by:
Hi there, Using PHPMyAdmin and it is very usefully reporting problems with my MySQL DB. "PRIMARY and INDEX keys should not both be set for column `column_name`" and
8
11607
by: pb648174 | last post by:
I have a single update statement that updates the same column multiple times in the same update statement. Basically i have a column that looks like .1.2.3.4. which are id references that need to be updated when a group of items is copied. I can successfully do this with cursors, but am experimenting with a way to do it with a single update statement. I have verified that each row being returned to the Update statement (in an...
5
4108
by: rdemyan via AccessMonster.com | last post by:
I have a need to add another field to all of my tables (over 150). Not data, but an actual field. Can I code this somehow. So the code presumabley would loop through all the tables, open each table in design mode and then add the new field and set its properties. Thanks. --
7
20341
by: =?Utf-8?B?QVRT?= | last post by:
HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g. Please help, I'm trying to write an ASP page to use ADO to run a long query against an Oracle 10g database, to create tables, if they do not already exist. In terms of ASP/ADO, that would be fine in a SQL Server Sense by a simply ASP/Server-Side JavaScript as such: var cnTemp = Server.CreateObject("ADODB.Connection");
7
15668
by: =?Utf-8?B?TG9zdEluTUQ=?= | last post by:
Hi All :) I'm converting VB6 using True DBGrid Pro 8.0 to VB2005 using DataGridView. True DBGrid has a MultipleLines property that controls whether individual records span multiple lines. Is there an equivalent property for the DataGridView? I have searched, but have not found one. I would like the user to be able to see all the columns of the table on one screen - thus eliminating the need to use the horizontal scroll bar to view...
25
2554
by: Darsin | last post by:
Hi all I need to perform a summation on a column of a table based on a criteria given in another column in the same table. The catch is i need to perform different sums according to the number of criterias in the criteria column, in a single query. the structure of the table is somethinmg like this (only relevant columns are shown) TABLE1 Value - numeric(20,6) Month - int
1
2652
by: ll | last post by:
Hi all, I've inherited a site and am currently looking to redesign a page that displays a table of course curriculum, with each row representing a "Topic" of the curriculum. There is a courseID that exists throughout the site, identifying each course. Within each row of the aforementioned table, there are 4 columns, consisting of MainTopics, their associated SubTopics, uploaded LectureNotes for that MainTopic, and finally a DeleteRow...
8
10237
by: modernshoggoth | last post by:
G'day all, thanks in advance for reading. I've got two tables, one that's full of data regularly externally updated called "tblEmployeeLicences": EmployeeID,Name,Licences 1001,Bill,Drivers 1001,Bill,Forklift 1002,Ted,Drivers 1002,Ted,Forklift 1002,Ted,Crane The other table is one that is depended upon by an ID-card printing program, and as such only allows a single row per employee, called "IDProjectData":
0
9826
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11234
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10925
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7171
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5854
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6058
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4682
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4276
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3282
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.