Update Logic [message #665781] |
Wed, 20 September 2017 09:54 |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Hi thanks for your previous posts and suggestions on my questions.
I have a doubt over an update logic. Request your suggestion please.
I want to update the ind column with 'TD' where code='5GZ' and 'TR' where code='928'.
If a clcl_id is having more than one seq_no and the code is same I should not update that row.
If a clcl_id is having more than one seq_no and the codes are different I need to update to 'TX'
The below is code is working fine. But is there any way that the above can be done in a SINGLE update statement.
create table test_clcl(clcl_id number,seq_no number,code varchar2(3), ind varchar2(2));
insert into test_clcl values(1000,1,'5GZ',null);
insert into test_clcl values(1000,2,'928',null);
insert into test_clcl values(1001,1,'5GZ',null);
insert into test_clcl values(1001,2,'5GZ',null);
insert into test_clcl values(1002,1,'5GZ',null);
insert into test_clcl values(1003,1,'928',null);
update test_clcl set ind='TX'
where clcl_id in (select a.clcl_id,a.code from test_clcl a,test_clcl b
where a.clcl_id = b.clcl_id and a.code<>b.code);
update test_clcl set ind = case when code='5GZ' then 'TD'
when code='928' then 'TR' end
where clcl_id in (select a.clcl_id from test_clcl a,test_clcl b
where a.clcl_id = b.clcl_id and a.code=b.code group by a.clcl_id having count(1) =1);
select * from test_clcl order by 1;
your suggestion is highgly appreciated.
Thanks.
|
|
|
|
|
|
Re: Update Logic [message #665788 is a reply to message #665781] |
Wed, 20 September 2017 11:34 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
pstanand wrote on Wed, 20 September 2017 10:54
The below is code is working fine.
I don't think so:
SQL> update test_clcl set ind='TX'
2 where clcl_id in (select a.clcl_id,a.code from test_clcl a,test_clcl b
3 where a.clcl_id = b.clcl_id and a.code<>b.code);
where clcl_id in (select a.clcl_id,a.code from test_clcl a,test_clcl b
*
ERROR at line 2:
ORA-00913: too many values
SQL>
SQL> update test_clcl set ind = case when code='5GZ' then 'TD'
2 when code='928' then 'TR' end
3 where clcl_id in (select a.clcl_id from test_clcl a,test_clcl b
4 where a.clcl_id = b.clcl_id and a.code=b.code group by a.clcl_id having cou
nt(1) =1);
2 rows updated.
SQL> select * from test_clcl order by 1;
CLCL_ID SEQ_NO COD IN
---------- ---------- --- --
1000 1 5GZ
1000 2 928
1001 1 5GZ
1001 2 5GZ
1002 1 5GZ TD
1003 1 928 TR
6 rows selected.
SQL>
As you can see, IND for CLCL_ID= 1000 and 1001 is NULL while, according to rules you posted, it should be TX. If so, use something like:
SQL> update test_clcl a
2 set ind = case (select count(distinct b.code) from test_clcl b where b.clcl_id = a.clcl_id)
3 when 1 then case code
4 when '5GZ' then 'TD'
5 when '928' then 'TR'
6 else ind
7 end
8 else 'TX'
9 end
10 /
6 rows updated.
SQL> select * from test_clcl order by 1;
CLCL_ID SEQ_NO COD IN
---------- ---------- --- --
1000 1 5GZ TX
1000 2 928 TX
1001 1 5GZ TD
1001 2 5GZ TD
1002 1 5GZ TD
1003 1 928 TR
6 rows selected.
SQL>
SY.
|
|
|
Re: Update Logic [message #665803 is a reply to message #665788] |
Thu, 21 September 2017 00:26 |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Quote:
If a clcl_id is having more than one seq_no and the code is same I should not update that row.
Hi, since the 1001 is having two seq_no with same code I should not update it.
Do you think your single update will do this? Thanks for your suggestion.
CLCL_ID SEQ_NO COD IN
---------- ---------- --- --
1000 1 5GZ TX
1000 2 928 TX
1001 1 5GZ NULL
1001 2 5GZ NULL
1002 1 5GZ TD
1003 1 928 TR
|
|
|
|
|