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

[ajay]

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

[Serge Rielau]

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

[Matt Emmerton]

"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