Hello,

I'm having trouble to 'translate' a select-statement, which provides me with the correct information, into an Update-statement.

Basically, in case of a merge of 2 items, I want to update a date-field for the item that remains after the merge, with a date-field of the item that'll 'vanish', but only when the date of the 'vanishing' item is greater than that of the remaining item.

The statement that locates these items is:

select temp.bsstammnr, temp.schlinh, temp.schlinh1, temp.max_dat
from (select cast(a.SCHLINH / 100 as integer) as bsstammnr,
a.schlinh as schlinh,
a.schlinh1 as schlinh1,
max(b.datum3) as max_dat
FROM r002090v a, r002090v b
WHERE a.vertraend = '3'
and a.tarif1 in ('11', '12', '21', '22', '23', '24')
and year(a.anldat) = '2010'
and a.mkm = 0
and a.schlinh1 = b.schlinh
and b.schlinh1 = a.schlinh
group by cast(a.SCHLINH / 100 as integer),
a.schlinh, a.schlinh1) as temp, r002090v c
where temp.schlinh = c.schlinh
and temp.schlinh1 = c.schlinh1
and temp.max_dat > c.datum3
order by bsstammnr

This query provides the max_dat of the serial number (bsstammnr), within which 1 or more items have been merged to the remaining item (schlinh). It also provides the vanishing item's identifier (schlinh1).

The results of the above query shows that the date of 5 items need to be updated:

BSSTAMMNR SCHLINH SCHLINH1 MAX_DAT
27,947 2,794,702 2,794,713 20,051,125
147,601 14,760,102 14,760,103 20,071,127
711,055 71,105,502 71,105,503 20,071,023
711,503 71,150,301 71,150,302 20,070,928
714,056 71,405,602 71,405,601 20,080,111

And the UPDATE statement is causing me loads of trouble. Whatever I try, I get: Result of SELECT more than one row... I did the sensible thing (I think, I'm not an expert) to not refer to the table I want to update within the SELECT statement. But whatever I try... I'm sure the figures -811 and 21000 will haunt me for a couple days. ;-))

Here's the UPDATE statement:

Update r000400v x
Set x.sbeginndat = (select temp.max_dat
from (select cast(a.SCHLINH / 100 as integer) as bsstammnr,
a.schlinh as schlinh,
a.schlinh1 as schlinh1,
max(b.datum3) as max_dat
FROM r002090v a, r002090v b
WHERE a.vertraend = '3'
and a.tarif1 in ('11', '12', '21', '22', '23', '24')
and year(a.anldat) = '2010'
and a.mkm = 0
and a.schlinh1 = b.schlinh
and b.schlinh1 = a.schlinh
group by cast(a.SCHLINH / 100 as integer),
a.schlinh, a.schlinh1) as temp, r002090v c
where temp.schlinh = c.schlinh
and temp.schlinh1 = c.schlinh1
and temp.max_dat > c.datum3)


where x.bsstammnr * 100 + x.bsvnr in (select temp.schlinh
from (select cast(a.SCHLINH / 100 as integer) as bsstammnr,
a.schlinh as schlinh,
a.schlinh1 as schlinh1,
max(b.datum3) as max_dat
FROM r002090v a, r002090v b
WHERE a.vertraend = '3'
and a.tarif1 in ('11', '12', '21', '22', '23', '24')
and year(a.anldat) = '2010'
and a.mkm = 0
and a.schlinh1 = b.schlinh
and b.schlinh1 = a.schlinh
group by cast(a.SCHLINH / 100 as integer),
a.schlinh, a.schlinh1) as temp, r002090v c
where temp.schlinh = c.schlinh
and temp.schlinh1 = c.schlinh1
and temp.max_dat > c.datum3)

This bit: "x.bsstammnr * 100 + x.bsvnr" represents the remaining item after merge in the target table.

If anyone'd have any suggestions, I'd be very grateful. Thanks in advance for having a look!

Gr. W.

I'm fairly certain that the subquery in the SET clause of your UPDATE statement:
is missing a correlation condition (specifying the link between x, the table to be updated, and the tables temp and c of the subquery.
Adding some "WHERE x.col = c.col AND ..." at the very end of the above subquery should achieve what you want.
Maybe there's a PK-FK relation between tables r000400v and r002090v ?

The "-811" tells you that you try to update column x.sbeginndat with more than one value (viz all values temp.max_dat returned by the above query).
If you're sure there is no correlation involved, i.e., that you want all matching rows of x to be updated with the same max_dat value, maybe you want "select MAX(temp.max_dat)" in the subquery?

Your missing correlation condition in the SET subquery might be
AND a.schlinh = x.bsstammnr * 100 + x.bsvnr

Like...
Note 1: I capitalized keywords like UPDATE, SELECT, FROM, so on...
Note 2: It may be not neccesary to include the expression cast(a.SCHLINH / 100 as integer)
in SELECT clause and GROUP BY clause.
Because, a.SCHLINH used in the expresion was a GROUP BY column.
Note 3: I removed quotation marks in
YEAR(a.anldat) = '2010'
Because, the result of a YEAR() function is INTEGER.
I guessed that DB2 might do datatype conversion implicitly in your original query.

I have a question.

Is it not necessary to specify same conditions for r002090v b and r002090v c as the conditions for r002090v a?
If it is not necessary, why?

I have read again your requirements,
and I guessed...

1) number of the remaining item in a serial number (bsstammnr) was one.
And the candidates of remaining items would be the rows satisfying the conditions:
WHERE a.vertraend = '3'
and a.tarif1 in ('11', '12', '21', '22', '23', '24')
and year(a.anldat) = '2010'
and a.mkm = 0

Is it right?

2) the number of vanishing items in a serial number (bsstammnr) were zero, one, or more.
And the vanishing items(b) are known from the remaining item(a) by the conditions:
... b.schlinh = a.schlinh1
and b.schlinh1 = a.schlinh

(If no vanishing item was there, then update would not be necessary for the serial number (bsstammnr).)

Is it right?


If my guess was right,
this example might give you same results as your first query.
and this might be worth to try.
You can see the expected results of the UPDATE statement,
by modifying the UPDATE statement like this...