Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2013
    Posts
    1

    Filtering Duplicate Data

    We need to insert into master table whenever there is a change in any column data in stage table.

    The stage table has 10 columns including lastupdate column. Please help me on this.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,650
    can you write SQL when you don't know table or column names?
    I can't.

    How does post title "Filtering duplicate Date" come into play with other requirements?
    What criteria must be met for rows to be considered to "duplicate"?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,009
    This:
    Quote Originally Posted by m_tubati View Post
    We need to insert into master table whenever there is a change in any column data in stage table.
    looks like a database trigger would do the job. Here's an example: first, create an empty "master" table (the original Scott's DEPT table is your "stage" table, then):
    Code:
    SQL> create table my_dept as select * from dept where 1 = 2;
    
    Table created.
    
    SQL> create or replace trigger trg_bu_dept
      2    before update on dept
      3    for each row
      4  begin
      5    insert into my_dept
      6      (deptno, dname, loc)
      7      values
      8     (:old.deptno, :old.dname, :old.loc);
      9  end;
     10  /
    
    Trigger created.
    Let's try it:
    Code:
    SQL> select * from my_dept;
    
    no rows selected
    
    SQL> select * from dept;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL> update dept set dname = 'sales' where deptno = 30;
    
    1 row updated.
    
    SQL> select * from my_dept;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            30 SALES          CHICAGO
    
    SQL> select * from dept;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 sales          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL>
    As of "duplicate data", I'm as puzzled as much as Anacedent is.

Posting Permissions

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