Home » SQL & PL/SQL » SQL & PL/SQL » Update Logic (Oracle,12c,NT)
Update Logic [message #665781] Wed, 20 September 2017 09:54 Go to next message
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 #665782 is a reply to message #665781] Wed, 20 September 2017 10:15 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Which update takes precedence when there's a row that satisfies both sets of criteria?
Re: Update Logic [message #665786 is a reply to message #665782] Wed, 20 September 2017 11:07 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
First update
Re: Update Logic [message #665787 is a reply to message #665786] Wed, 20 September 2017 11:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
pstanand wrote on Wed, 20 September 2017 09:07
First update
Then you had better run it after the "second" UPDATE is invoked.
Re: Update Logic [message #665788 is a reply to message #665781] Wed, 20 September 2017 11:34 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
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
Re: Update Logic [message #665804 is a reply to message #665803] Thu, 21 September 2017 01:20 Go to previous messageGo to next message
browncat
Messages: 9
Registered: May 2015
Junior Member
Kindly see these queries, they will work for the update statement....

UPDATE test_clcl set ind = "TD" where code = "5GZ";
UPDATE test_clcl set ind = "TR" where code = "928";
Re: Update Logic [message #665807 is a reply to message #665804] Thu, 21 September 2017 06:45 Go to previous message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi, it works fine... many thanks for your suggestions.
Previous Topic: ORA-06530: Reference to uninitialized composite
Next Topic: Need Date format in YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00)
Goto Forum:
  


Current Time: Thu Mar 28 17:23:01 CDT 2024