"Ricky" <iq****@hotmail.comwrote in message
news:a9**************************@posting.google.c om...
"Ievel" <ie***************@skynet.bewrote in message
news:<3f*********************@reader1.news.skynet. be>...
"Ricky" <iq****@hotmail.comwrote in message
news:a9**************************@posting.google.c om...
HI, I have a question given that I have a table T
>
en1_id | en1 | en2_id | en2
1 A (empty) B
2 B (empty) C
3 C (empty) D
>
eventually I want to update en2_id according to en1 which mean if
en2=en1 set en2_id to en1_id. If en2 is not in en1 then create a new
id for en2_id
>
the end table looks like
en1_id | en1 | en2_id | en2
1 A 2 B
2 B 3 C
3 C 4 D
>
I first try to build a view
create view VT as select * from T;
>
then I do update
update T
set en2_id=en1_id
where T.en2 in(select VT.en1 from VT);
>
but this give me something like
1 A 1 B
2 B 2 C
3 C e D
It just set en2_id=en1_id on the same row instead of setting it to the
corresponding row.
>
is there any way to update the table to the way I wanted
>
Thanks alot !!!
>
Ricky
I haven't been able to test the following and you probably can find more
performant statements, but it think it does the job:
UPDATE T t1
SET en2_id = DECODE(SELECT 1
FROM T t2
WHERE t2.en1 = t1.en2
,1,(SELECT en1_id
FROM T t4
WHERE t4.en1 = t1.en2)
,(SELECT MAX(t3.en1_id)+1
FROM T t3)
);
ieVel
Thank you for your reply,
However, when I put in your query I got error
SQLupdate en10p t1
2 set en2_id=DECODE((select 1 from en10p t2
3 where t2.en1=t1.en2)
4 ,1,(select en1_id from en10p t4
5 where t4.en1=t1.en2)
6 ,(select max(t3.en1_id)+1
7 from en10p t3)
8 );
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
Forgot to mention that the example I given is just an example the
realy table has more than 200K entries thus it might return more than
1 row in the subquery. Is there any way to over come this.
Thanks
Indeed I made a small error.
I think this version should be better.
But I think that with 200K rows performance won't be great...
Maybe making a small procedure using a cursor will be lot faster...
UPDATE T t1
SET en2_id = DECODE((SELECT 1
FROM T t2
WHERE t2.en1 = t1.en2
AND ROWNUM < 2)
,1,(SELECT MIN(en1_id)
FROM T t4
WHERE t4.en1 = t1.en2)
,(SELECT MAX(t3.en1_id)+1
FROM T t3)
);
ieVel