470,647 Members | 1,197 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,647 developers. It's quick & easy.

How to write a single update sql statement to update different setof attributes of a table

How to write a single update sql statement to update different set of
attributes of a table. In other words what will be the value of
indicator variables to ignore updates of some attributes in a update
statement.

e.g. EXEC SQL UPDATE
vA:iA
vB:iB
vC:iC
in TABLE 1 where vD = ...
What should be the indicator values in the above statement if i want to
update only vA and vB but not vC in the database table.

Thanks
galbodada

Jul 19 '05 #1
2 23480
ajay wrote:
How to write a single update sql statement to update different set of
attributes of a table. In other words what will be the value of
indicator variables to ignore updates of some attributes in a update
statement.

e.g. EXEC SQL UPDATE
vA:iA
vB:iB
vC:iC
in TABLE 1 where vD = ...
What should be the indicator values in the above statement if i want to
update only vA and vB but not vC in the database table.

Thanks
galbodada

You can use CASE expression for few columns or UNION ALL for many.
UPDATE T
SET (c1, c2, ... cn)
= (SELECT <expr1>, ... <expri>, ci+1, ..., cn
FROM TABLE(VALUES(1) AS A
WHERE <prd>
UNION ALL
SELECT c1, ..., ci, <expri+1>, ..., <exprn>
FROM TABLE(VALUES 1) AS B
WHERE NOT <prd>)
WHERE ...

Not sure whether TABLE(VALUES ..) is ANSI SQL. You can use whatever
your DBMS supplies to generate a single row table (SYSIBM.SYSDUMMY1,
DUAL, VALUES(1))

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Jul 19 '05 #2

"ajay" <aj**@speedfactory.net> wrote in message
news:40**************@speedfactory.net...
How to write a single update sql statement to update different set of
attributes of a table. In other words what will be the value of
indicator variables to ignore updates of some attributes in a update
statement.

e.g. EXEC SQL UPDATE
vA:iA
vB:iB
vC:iC
in TABLE 1 where vD = ...
What should be the indicator values in the above statement if i want to
update only vA and vB but not vC in the database table.

Thanks
galbodada


You can do this with CASE statements.

The general form for this type of update:

EXEC SQL UPDATE <tablename>
SET <colA> CASE WHEN <indA> = 1 THEN <valA> ELSE <colA> END,
<colB> CASE WHEN <indB> = 1 THEN <valB> ELSE <colB> END,
....
<colQ> CASE WHEN <indQ> = 1 THEN <valQ> ELSE <colQ> END
WHERE <colX> = <valX> AND <colY> = <colY>;

And a specific example for a table containing customer information:

EXEC SQL UPDATE customer
SET c_first CASE WHEN :c_first_indicator = 1 THEN :c_first_value ELSE
c_first END,
c_last CASE WHEN :c_last_indicator = 1 THEN :c_last_value ELSE c_last END,
c_middle CASE WHEN :c_middle_indicator = 1 THEN :c_middle_value ELSE
c_middle END
WHERE c_id = :c_id;

If the c_first_indicator host variable is set to 1, then the c_first column
will be updated to the value of the c_first_value host variable, otherwise
the column is updated with the the current value. Similarly for other
columns.

--
Matt Emmerton
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Stephen Plotnick | last post: by
1 post views Thread by Korara | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.