Thomas Cataldo wrote:
>
> Hi,
>
> Here is my problem :
>
> CREATE TABLE toc_tree (
> id serial primary key,
> lft int,
> rgt int
> )
> INSERT INTO toc_tree (lft, rgt) VALUES (1, 2)
>
> My table has the following content :
> id lft rgt
> 1 1 2
>
> Now I want to do the following update :
>
> UPDATE toc_tree
> SET
> lft = (CASE WHEN lft > 2 THEN lft + 2 ELSE lft END),
> rgt = (CASE WHEN rgt >= 2 THEN rgt + 2 ELSE rgt END)
> WHERE rgt >= 2
>
> Here is what I expect :
> id lft rgt
> 1 1 4
>
> And here is what I get :
> id lft rgt
> 1 3 4
>
> Can anybody tell me if I am missing the obvious, or if there is some
> "magic" here...
>
> This is on maxdb 7.5.0.5
> I will try it on sapdb 7.4.0.30 tomorrow.
>
> Thomas.
Forget about testing 7.4.3, it is a bug in all versions,
when UPDATE is used and CASE in SET-clause.
Thank you for reporting. It will be fixed with one of the next versions.
In the meantime perhaps a workaround like this can help:
(I do not like it either, but better than not doing the update, but please
revert when using the version with the bug-fix)
UPDATE TOC_TREE
SET
LFT = (SELECT
CASE WHEN TOC_TREE.LFT > 2
THEN TOC_TREE.LFT + 2
ELSE TOC_TREE.LFT END
FROM DUAL),
RGT = (SELECT
CASE WHEN TOC_TREE.RGT >= 2
THEN TOC_TREE.RGT + 2
ELSE TOC_TREE.RGT END
FROM DUAL)
WHERE RGT >= 2
or (which may be better for performance than the one above):
UPDATE TOC_TREE
SET
LFT = DECODE (SIGN (LFT-2),
1, LFT + 2,
LFT),
RGT = DECODE (SIGN(RGT-1),
1, RGT+2,
RGT)
WHERE RGT>=2
(I prefer the last one, although there is a little trick with SIGN.)
BTW: why do you have thise case/decode/subquery for RGT
if only values which will be increased by 2 are qualified? Why not just do
RGT = RGT+2?
Is this a problem of simplifying an existing update for sending it to the list?
Elke
SAP Labs Berlin
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
