AW: sql error -199 sqlstate 42601

bernd oppolzer

AW: sql error -199 sqlstate 42601
I would like to add that this UPDATE statement
seems a little bit dangerous to me ... if you never heard
about the Halloween problem, you should probably read this:

https://en.wikipedia.org/wiki/Halloween_Problem

This SQL could IMO easily be affected by Halloween effects,
due to the fact, that the column which controls the condition
of the subquery (LOG_TS) is subject of the UPDATE, too.

I would prefer it, if the corrective action would be done in two steps,
in this case. First, the keys of the records to be UPDATEs should be
SELECTed and written to a file, and then the UPDATEs should be scheduled,
based on the retrieved keys.

Kind regards

Bernd


Am 09.05.2019 um 00:21 schrieb Bernd Oppolzer:
>
> Don't know if this is what you want to do:
>
>
> UPDATE B3ITEM.ITEM_log a
>    SET A.LOG_TS = a.LAST_UPDATE_TS
>  WHERE A.LOG_TS =
>        (SELECT MAX (LOG_TS)
>           FROM B3ITEM.ITEM_log b
>          WHERE b.item_nbr = a.item_nbr)
>    and a.LAST_CHG_USER_ID = 'IT230X';
>
> This UPDATE should for every item_nbr update the row with the highest
> LOG_TS (I hope that there is only one such row) if the LAST_CHG_USER_ID
> is 'IT230X'. It sets the LOG_TS to the value of LAST_UPDATE_TS.
>
> No join needed; the condition is enforced by the correlated subquery.
>
> Others may look at this to find logic or syntactic errors in it;
> and you should check if that really is what you want.
>
> HTH, kind regards
>
> Bernd
>
>
>
> Am 08.05.2019 um 14:50 schrieb Ron Thomas:
>>
>> So any idea how this query can be modified to give the desired result
>> . I need to get the latest row updated for the item_log table for the
>> item_nbrs.
>>
>> Regards
>>
>> Ron T
>>
>>
>> -----End Original Message-----