Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: Update a Record if not matching?

    Code:
    drop table temppp;
    create table temppp(uniqueid number,employeeid varchar2(15),sname varchar2(90),acode varchar2(1),
    adesc varchar2(30),start_date date,end_date date,rind varchar(1));
    
    insert into temppp values(1,10,'Java','1','ABC',to_date('2011-10-11','YYYY-MM-DD'),
    to_date('2011-10-11','YYYY-MM-DD'),'N');
    
    insert into temppp values(2,10,'Sun Java','2','XYZ',to_date('2011-10-12','YYYY-MM-DD'),
    to_date(NULL,'YYYY-MM-DD'),'Y');
    
    Select * from temppp;
    
    Update temppp a set adesc=
    (select nvl(a.adesc,'M XYZ') 
    from temppp b where a.uniqueid= b.uniqueid 
    and a.employeeid= b.employeeid
    and a.acode=2
    and a.rind='Y'
    and b.rind='N')
    where a.acode=2
    and a.end_date is null
    i have requirement if a uniqueid changes for the same employee id i need to update the Adesc to "M XYZ". so far i have done this qry to update.

    Please suggest me.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As your table contains some columns you didn't describe, here's my own test case:
    Code:
    SQL> select * from test
      2  order by empid, start_date;
    
         EMPID     UNIQID START_DATE ADESC
    ---------- ---------- ---------- --------------------
            10          1 01.10.2011 A
            10          1 02.10.2011 B
            10          2 03.10.2011 C    -> should be modified because UNIQID changed from 1 -> 2
            10          3 04.10.2011 D    -> should be modified because UNIQID changed from 2 -> 3
            20          1 05.10.2011 E
            20          2 06.10.2011 F    -> should be modified because UNIQID changed from 1 -> 2
    
    6 rows selected.
    
    SQL>
    You said that ADESC column should be changed if UNIQID is changed for an employee; I supposed records are ordered by START_DATE (otherwise, you should explain what does that "change" mean - change from what to what?).

    In that case, one option might be to use the LAG analytical function:
    Code:
    SQL> update test s set
      2    s.adesc = 'M XYZ'
      3    where (s.empid, s.uniqid, s.start_date) in
      4          (select x.empid, x.uniqid, x.start_date
      5           from (select t.empid, t.uniqid, t.start_date,
      6                        lag(t.uniqid) over (partition by t.empid
      7                                             order by t.empid, t.start_date
      8                                            ) next_uniqid
      9                 from test t
     10                ) x
     11           where x.uniqid <> x.next_uniqid
     12          );
    
    3 rows updated.
    
    SQL> select * from test
      2  order by empid, start_date;
    
         EMPID     UNIQID START_DATE ADESC
    ---------- ---------- ---------- --------------------
            10          1 01.10.2011 A
            10          1 02.10.2011 B
            10          2 03.10.2011 M XYZ
            10          3 04.10.2011 M XYZ
            20          1 05.10.2011 E
            20          2 06.10.2011 M XYZ
    
    6 rows selected.
    
    SQL>

  3. #3
    Join Date
    Jul 2008
    Posts
    94
    Thank you Littlefoot.

    Code:
    drop table temppp;
    create table temppp(uniqueid number,employeeid varchar2(15),sname varchar2(90),acode varchar2(1),
    adesc varchar2(30),start_date date,end_date date,rind varchar(1));
    
    insert into temppp values(1,10,'Java','1','I',to_date('2011-10-11','YYYY-MM-DD'),
    to_date('2011-10-11','YYYY-MM-DD'),'N');
    insert into temppp values(2,10,'Sun Java','2','U',to_date('2011-10-12','YYYY-MM-DD'),
    to_date(NULL,'YYYY-MM-DD'),'Y');
    insert into temppp values(3,10,'Unix','1','I',to_date('2011-10-11','YYYY-MM-DD'),
    to_date('2011-10-11','YYYY-MM-DD'),'N');
    insert into temppp values(3,10,'Unix Shell','2','U',to_date('2011-10-12','YYYY-MM-DD'),
    to_date(NULL,'YYYY-MM-DD'),'Y');
    insert into temppp values(4,10,'ProC','1','I',to_date('2011-10-11','YYYY-MM-DD'),
    to_date('2011-10-11','YYYY-MM-DD'),'N');
    insert into temppp values(5,10,'ProC','2','U',to_date('2011-10-12','YYYY-MM-DD'),
    to_date(NULL,'YYYY-MM-DD'),'Y');
    insert into temppp values(6,10,'Oracle','1','I',to_date('2011-10-11','YYYY-MM-DD'),
    to_date('2011-10-11','YYYY-MM-DD'),'N');
    insert into temppp values(6,10,'Oracle','1','I',to_date('2011-10-12','YYYY-MM-DD'),
    to_date(NULL,'YYYY-MM-DD'),'Y');
    insert into temppp values(7,11,'Oracle','1','I',to_date('2011-10-11','YYYY-MM-DD'),
    to_date('2011-10-11','YYYY-MM-DD'),'N');
    insert into temppp values(8,11,'Oracle','2','U',to_date('2011-10-12','YYYY-MM-DD'),
    to_date(NULL,'YYYY-MM-DD'),'Y');
    1. When Skill Name and Unique ID changes then no need to update
    2. When Skill Name Changes and Unique ID is same then no need to update
    3. Only if Unique ID Changes then Update ACODE=6 and ADESC= M XYZ Where END_DATE IS NULL and ACODE=2;

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK; I suppose you should be able to include these (END_DATE IS NULL AND ACODE = 2) conditions into the above UPDATE statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •