updating table from temp table

Thanos

hello,

I've created a temp table within a stored pprocedure which was updated
and changed. However I need to update an existing table with some
column from the temp table. Below is my update statement which does
not work. I get the following error:

DB2ADMIN.IN_CFD: 111: [IBM][CLI Driver][DB2/NT] SQL0104N An
unexpected token "from session" was found following
"emp_cfd.exclude_ind ".

update cfd_masterfile
set record_source = session.temp_cfd.record_src,
insight_ind = session.temp_cfd.insight_ind,
exclude_ind = session.temp_cfd.exclude_ind
from session.temp_cfd
where (account_number = session.temp_cfd.account_number) and
(flag_update_cf = 'Y');
commit;

Nov 12 '05 #1

Knut Stolze

Thanos wrote:

hello,

I've created a temp table within a stored pprocedure which was updated
and changed. However I need to update an existing table with some
column from the temp table. Below is my update statement which does
not work. I get the following error:

DB2ADMIN.IN_CFD: 111: [IBM][CLI Driver][DB2/NT] SQL0104N An
unexpected token "from session" was found following
"emp_cfd.exclude_ind ".

update cfd_masterfile
set record_source = session.temp_cfd.record_src,
insight_ind = session.temp_cfd.insight_ind,
exclude_ind = session.temp_cfd.exclude_ind
from session.temp_cfd
where (account_number = session.temp_cfd.account_number) and
(flag_update_cf = 'Y');
commit;

Have a look at the syntax for the UPDATE statement. You need something
like:

UPDATE ...
SET (col1, col2, col3) = ( SELECT ...
FROM session.temp_cfd
WHERE ... )
WHERE ...

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Nov 12 '05 #2

Thanos

hello,

here is my problem,

I need to update a large table from the rows of a temporary table. So
i used a cursor as the field values for the updates will change but it
don't work. I get an error with the fetch c1; statement.
declare c1 cursor for

select record_source, insight_ind, exclude_ind
from cfd_masterfile
where record_source <> 'PCG'
for update of record_source, insight_ind, exclude_ind;

open c1;
fetch c1;
update cfd_masterfile
set (record_source, insight_ind, exclude_ind) = (select
temp_both.record_src, temp_both.insight_ind, temp_both.exclude_ind
from session.temp_both temp_both
where cfd_masterfile.account_number =
session.temp_both.account_number)
where current of c1;

Nov 12 '05 #3

Serge Rielau

Thanos,

It should be FETCH c1 INTO <host variables>
Why do you use a cursor in the first place?
Try this:

UPDATE cfd_masterfile
set (record_source, insight_ind, exclude_ind) = (select
temp_both.record_src, temp_both.insight_ind, temp_both.exclude_ind
from session.temp_both temp_both
where cfd_masterfile.account_number =
session.temp_both.account_number)
where record_source <> 'PCG';

Cheers
Serge

Nov 12 '05 #4

Thanos

Serge,

Sorry i wasn't as clear but this is a positioned update of each
record.
The temp table was used to change values of 3 columns but the values
of these columns for each record are different. So when i try to
update the master table from the temp table i thought i need to use a
cursor so that each row would be evaluated separately. Your thoughts
are greatly appreciated.

WofD
Serge Rielau <sr*****@ca.eye-bee-em.com> wrote in message news:<41********@news3.prserv.net>...

Thanos,

It should be FETCH c1 INTO <host variables>
Why do you use a cursor in the first place?
Try this:

UPDATE cfd_masterfile
set (record_source, insight_ind, exclude_ind) = (select
temp_both.record_src, temp_both.insight_ind, temp_both.exclude_ind
from session.temp_both temp_both
where cfd_masterfile.account_number =
session.temp_both.account_number)
where record_source <> 'PCG';

Cheers
Serge

Nov 12 '05 #5

Serge Rielau

OK, let me blindly transalte what I presume to be Sybase/SQL Server or
IDS Syntax:
MERGE INTO cfd_masterfile
USING session.temp_cfd
ON (account_number = session.temp_cfd.account_number) and
(flag_update_cf = 'Y')
WHEN MATCHED THEN UPDATE
set record_source = session.temp_cfd.record_src,
insight_ind = session.temp_cfd.insight_ind,
exclude_ind = session.temp_cfd.exclude_ind;

Cheers
Serge

Nov 12 '05 #6