UPDATE MULTIPLE COLUMNS

[Ann28]

Hi everyone!
Please help!

1) ACT_VAL_04_TOT tbl to be updated
Columns:
SSN, (populated already)
EARN, (populated already)
HRS, (populated already)
Q1_EARN, (needs to be updated)
Q1_HRS, (needs to be updated)
Q2_EARN,
Q2_HRS,
Q3_EARN,
Q_3_HRS,
Q4_EARN,
Q4_HRS

Here is the table contains the values to update the
ACT_VAL_04_TOT with
SSN
EARN
HRS
QTR
Where earn = $earing, HRS=$HRS & QTR represents 1,2,3,4 meaning quarters

Here is my update statement:

update   BASYS.ACT_VAL_04_TOT set (ACT_VAL_04_TOT.Q1_EARN,  ACT_VAL_04_TOT.Q1_HRS)=
(select  ACT_VAL_04_QTR.EARN,ACT_VAL_04_QTR.HRS
FROM BASYS.ACT_VAL_04_QTR , BASYS.ACT_VAL_04_TOT
WHERE ACT_VAL_04_QTR.SSN = ACT_VAL_04_TOT.SSN AND ACT_VAL_04_QTR.QTR = 1);

…..& the error:

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.

 

[Hattusas]

(select ACT_VAL_04_QTR.EARN,ACT_VAL_04_QTR.HRS
FROM BASYS.ACT_VAL_04_QTR , BASYS.ACT_VAL_04_TOT
WHERE ACT_VAL_04_QTR.SSN = ACT_VAL_04_TOT.SSN AND ACT_VAL_04_QTR.QTR = 1);

this returns more than one row. So you will need to modify your sentence. You can try grouping your results.

Salih Sipahi
Software Engineer.
City of Istanbul Turkey
s.sipahi@sahinlerholding.com.tr