Update problem

[Pekka]

Hello,
I'm trying to update field bkval1 in empeqpi0 with a calculated value from joined select. In both files there is only one row for idno1 and idno for 76U5261. When I run this statement it updates all the rows in empeqpi0 now matter what the idno1 is. Something wrong with the join?

UPDATE LIBZ95/Empeqpi0 SET bkval1 =
(Select
Bkval1+tramount From
Libz95/Empeqpi0 A, Witekr3/ems_trans B
where a.idno1=B.idno
And idno1='76U5261')

 

[MarcLodge]

Hi Pekka,
You have no WHERE clauses on the actual update, so the whole table will be updated.
Marc

 

[Pekka]

I admit that I'm little confused about the syntax. I'm using DB2 UDB for iSeries.(yes, quite neewbie). When I try the following syntax:

UPDATE LIBZ95/Empeqpi0 SET bkval1 = (Select
Bkval1+tramount
From
Libz95/Empeqpi0 A Inner join
Witekr3/ems_trans B On a.idno1=B.idno)
Where idno1='76U5261'

I get the message that Select Into- has produced more than one row.

 

[MarcLodge]

Pekka,

You can update many rows on a table with one value, but you cannot update one row/column with more than one value.

From what you say, if you take the sub select:
(Select
Bkval1+tramount
From
Libz95/Empeqpi0 A Inner join
Witekr3/ems_trans B On a.idno1=B.idno)
and run it on it's own, it will produce more than one value, which is not what you require.

It looks as if you wish to update the row with the idno1 = to 76U5261.

You need to bring this clause into both the update and the subselect which you can do by specifying it twice:

UPDATE LIBZ95/Empeqpi0 SET bkval1 =
(Select
Bkval1+tramount From
Libz95/Empeqpi0 A, Witekr3/ems_trans B
where a.idno1=B.idno
And idno1='76U5261')
where idno1 = '76U5261'

You could probably set up a join between the update and the subselect too if you wish. The only query I have on this is I'm not sure if you can subselect on a table that you wish to update. I have a nagging doubt at the back of my mind that you can't do this. Let me know how you get on.
Marc

 

[Pekka]

Thank you Marc, it works exactly the way i wished!