How to write a single update sql statement to update different set of 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]

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:40CD95A6...@speedfactory.net...

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