UPDATE TABLE'S VALUE BASED ON THE OTHER TABLE 3

[cristi22]

Please help with DB2 UPDATE:

I am trying to update the VALS_DV_SSA_06 table (ben amount) with the Amt
from the temp_rf.

The ben field is decimal (9,2) and the amt field is a varchar.

(1)

UPDATE VALS_DV_SSA_06 SET ben = 
( SELECT   double(cast (rtrim(amt) as char(5))) from temp_rf)
where ssn  in (select ssn from temp_rf  )

[RED]
ERROR:
UPDATE VALS_DV_SSA_06 SET ben = ( SELECT double(cast (rtrim(amt) as char(5))) as ben from temp_rf ) where ssn in (select ssn from temp_rf ) and fund='RFP'
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES
INTO statement is more than one row. SQLSTATE=21000

SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.
Explanation:
One of the following caused the error:
o Execution of an embedded SELECT INTO or VALUES INTO statement
resulted in a result table of more than one row.
o Execution of a scalar fullselect resulted in a result table
of more than one row.
Federated system users: this situation can be detected by
federated server or by the data source.
The statement cannot be processed.
User Response:
Ensure that the statement contains the proper condition
specifications. If it does, there may be a data problem that is
causing more than one row to be returned when only one is
expected.

Federated system users: isolate the problem to the data source
failing the request (refer to the problem determination guide to
determine which data source is failing to process the SQL
statement) and examine the selection criteria and data for that
object.

sqlcode : -811

sqlstate : 21000
[/RED]

(2)

UPDATE VALS_DV_SSA_06 D INNER JOIN temp_rf T ON D.SSN=T.SSN SET ben = double(cast (rtrim(amt) as char(5)))

this does not work at all
THANKS A LOT

C.

 

[onpnt]

Try using the DECIMAL function
like this

UPDATE VALS_DV_SSA_06 D 
SET ben = DECIMAL(amt)
INNER JOIN temp_rf T 
ON D.SSN=T.SSN

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[cristi22]

it did not work..

[IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "DECIMAL(amt) INNER JOIN temp_rf T ON" was found following "_SSA_06 D SET BEN =". Expected tokens may include: "<space>". SQLSTATE=42601

 

[blom0344]

Your error message refers to the fact that the update cannot work if:
1. No correlation exists between updated record and returned value.
2. Multiple values are returned from the select clause

So, if you have an uncorrelated query, then the select clause may return 1 value only (for the update)

Ties Blom

 

[cristi22]

still did not work

 

[blom0344]

update with correlation (no idea if you want that..):

UPDATE VALS_DV_SSA_06 V
SET V.ben = 
( SELECT decimal(amt) from temp_rf
                      where 
                      V.ssn = temp_rf.ssn)
where 
exists
(select 1 from  temp_rf
                where 
                V.ssn = temp_rf.ssn)

Ties Blom

 

[cristi22]

thank you Ties Blom