Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2012
    Posts
    8

    Unanswered: Quick Question about update statement

    Hello everyone,

    I have a quick question. I am using an update statement to make two seperate tables match exactly through an ETL. I do this by:

    UPDATE tableA
    SET
    FIELD 1 (select * From Table B where X)
    FIELD 2 (select * From Table B where X)
    FIELD 3 (select * From Table B where X)

    where EXISTS (select 1 from table where Table A field = table B field)

    The problem is I dont think the two tables match exactly because the table that is beeing copied could have deleted records that would not be deleted in the update statement. Is my concern valid?


    Eaxmple:


    Table A Table B
    1 A 1 A
    2 B 2 B
    3 C
    4d 4D

    (record 3c was deleted in table A but not "updated" in table B.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is my concern valid?
    yes

    what is justification for having 2 tables with same data?
    ever hear of Third Normal Form?
    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.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Only you will know if your concern is valid. It's validity depends on what you want the "COPY" table to hold.

    If you want table A to have the exact same rows as table B, then the simplest way is to:
    Code:
    drop table A;
    create table A as
       select * from B;
    If your source database is an Oracle database and you can connect to it from the destination database, then you should rather be using a materialized view. This would resolve all insert/update/delete in the most efficient way possible.

  4. #4
    Join Date
    Jul 2012
    Posts
    8
    To be honest i have not. Its a long storry but the duplicate table came from "oversight" .... Not by choice. That beeing said with an update statement I dont have a true picture of the table correct?

  5. #5
    Join Date
    Jul 2012
    Posts
    8
    Unfortunatly I cant drop tables. I can only append.



    Quote Originally Posted by dayneo View Post
    Only you will know if your concern is valid. It's validity depends on what you want the "COPY" table to hold.

    If you want table A to have the exact same rows as table B, then the simplest way is to:
    Code:
    drop table A;
    create table A as
       select * from B;
    If your source database is an Oracle database and you can connect to it from the destination database, then you should rather be using a materialized view. This would resolve all insert/update/delete in the most efficient way possible.

  6. #6
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Can you truncate the data, instead of dropping the table?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  7. #7
    Join Date
    Jul 2012
    Posts
    8
    Yes i can truncate the data. That shouldnt be a problem

  8. #8
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    My interpretation of what you want is:
    - You want table A to contain the exact same data as table B after the "process" has been run.
    - But you don't want to copy the entire source table of data every time. So, you only want to copy the changed data from the source table. But you don't know how to handle the deletes.

    Now I could go into a long description of how to keep two copies of data in sync. But Oracle has done that for us. It's called a materialized view.

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Quote Originally Posted by bmunos View Post
    Unfortunatly I cant drop tables. I can only append.
    If you cannot do this job due to lack of privileges, you may want to turn over this task to some other DBA that has adequate permissions to re-configure the ETL to do the truncate + load suggested, or even perhaps use materialized views instead.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    Jul 2012
    Posts
    8
    Not exactly. The ETL is taking data from several tables and inserting them into a single table. Then the statement below runs and updates any records that were existing and in need of updating.


    Quote Originally Posted by dayneo View Post
    My interpretation of what you want is:
    - You want table A to contain the exact same data as table B after the "process" has been run.
    - But you don't want to copy the entire source table of data every time. So, you only want to copy the changed data from the source table. But you don't know how to handle the deletes.

    Now I could go into a long description of how to keep two copies of data in sync. But Oracle has done that for us. It's called a materialized view.
    Last edited by bmunos; 07-18-12 at 12:18.

  11. #11
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    One thing though... If you are not able to drop a table, I am pretty sure you will not be able to create the Materialized View yourself. You will need a privileged user to assist in creation. But once created, there is no better way of keeping two tables in sync.

  12. #12
    Join Date
    Jul 2012
    Posts
    8
    great this is all good information. I think a Materialized View would work. Just doing a quick search it seems to fit my needs. I assume i can write a query that pulls in several fields from a table include some calculated or concatenated? Can I join two tables in a Materialized view?

  13. #13
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

  14. #14
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Post

    Example of materialized view
    Code:
    dayneo@RMSD> CREATE TABLE sourcetbl
      2  (
      3    id	  NUMBER,
      4    col_value  VARCHAR2(20),
      5    primary key (id)
      6  )
      7  /
    
    Table created.
    
    dayneo@RMSD> 
    dayneo@RMSD> CREATE MATERIALIZED VIEW LOG ON SOURCETBL
      2  NOCACHE
      3  LOGGING
      4  NOPARALLEL
      5  INCLUDING NEW VALUES
      6  /
    
    Materialized view log created.
    
    dayneo@RMSD> 
    dayneo@RMSD> CREATE MATERIALIZED VIEW copytbl
      2  BUILD IMMEDIATE
      3  REFRESH FAST ON DEMAND
      4  AS
      5  select * from sourcetbl
      6  /
    
    Materialized view created.
    
    dayneo@RMSD> 
    dayneo@RMSD> -- Fill the table with values
    dayneo@RMSD> insert into sourcetbl values(1, 'inserted value');
    
    1 row created.
    
    dayneo@RMSD> insert into sourcetbl values(2, 'inserted value');
    
    1 row created.
    
    dayneo@RMSD> insert into sourcetbl values(3, 'inserted value');
    
    1 row created.
    
    dayneo@RMSD> commit;
    
    Commit complete.
    
    dayneo@RMSD> 
    dayneo@RMSD> -- check what source table and copy table hold
    dayneo@RMSD> select * from sourcetbl;
    
            ID COL_VALUE
    ---------- --------------------
             1 inserted value
             2 inserted value
             3 inserted value
    
    dayneo@RMSD> select * from copytbl;
    
    no rows selected
    
    dayneo@RMSD> 
    dayneo@RMSD> -- Note that source table has 3 rows and copy table has no rows
    dayneo@RMSD> -- Run the materialized view refresh
    dayneo@RMSD> BEGIN
      2    DBMS_SNAPSHOT.REFRESH(
      3  	 LIST		      => 'COPYTBL'
      4  	,PUSH_DEFERRED_RPC    => TRUE
      5  	,REFRESH_AFTER_ERRORS => FALSE
      6  	,PURGE_OPTION	      => 1
      7  	,PARALLELISM	      => 0
      8  	,ATOMIC_REFRESH       => TRUE
      9  	,NESTED 	      => FALSE);
     10  END;
     11  /
    
    PL/SQL procedure successfully completed.
    
    dayneo@RMSD> 
    dayneo@RMSD> select * from sourcetbl;
    
            ID COL_VALUE
    ---------- --------------------
             1 inserted value
             2 inserted value
             3 inserted value
    
    dayneo@RMSD> select * from copytbl;
    
            ID COL_VALUE
    ---------- --------------------
             1 inserted value
             2 inserted value
             3 inserted value
    
    dayneo@RMSD> 
    dayneo@RMSD> -- Note that copy table now has the same data as source table
    dayneo@RMSD> -- Lets see what happens when updating/deleting
    dayneo@RMSD> update sourcetbl set col_value = 'updated value'
      2  where id = 2;
    
    1 row updated.
    
    dayneo@RMSD> 
    dayneo@RMSD> delete from sourcetbl where id = 3;
    
    1 row deleted.
    
    dayneo@RMSD> commit;
    
    Commit complete.
    
    dayneo@RMSD> -- Whats in the tables now?
    dayneo@RMSD> select * from sourcetbl;
    
            ID COL_VALUE
    ---------- --------------------
             1 inserted value
             2 updated value
    
    dayneo@RMSD> select * from copytbl;
    
            ID COL_VALUE
    ---------- --------------------
             1 inserted value
             2 inserted value
             3 inserted value
    
    dayneo@RMSD> -- Note that copy table still has old data
    dayneo@RMSD> -- Run the refresh again
    dayneo@RMSD> BEGIN
      2    DBMS_SNAPSHOT.REFRESH(
      3  	 LIST		      => 'COPYTBL'
      4  	,PUSH_DEFERRED_RPC    => TRUE
      5  	,REFRESH_AFTER_ERRORS => FALSE
      6  	,PURGE_OPTION	      => 1
      7  	,PARALLELISM	      => 0
      8  	,ATOMIC_REFRESH       => TRUE
      9  	,NESTED 	      => FALSE);
     10  END;
     11  /
    
    PL/SQL procedure successfully completed.
    
    dayneo@RMSD> -- Whats in the tables now?
    dayneo@RMSD> select * from sourcetbl;
    
            ID COL_VALUE
    ---------- --------------------
             1 inserted value
             2 updated value
    
    dayneo@RMSD> select * from copytbl;
    
            ID COL_VALUE
    ---------- --------------------
             1 inserted value
             2 updated value
    
    dayneo@RMSD> -- Note that copy table now has the same as sourcetbl

  15. #15
    Join Date
    Jul 2012
    Posts
    8
    I found the solution.

    The data is financial data. It follows GAAP so records cant ever be deleted... the update will work fine.

    Thanks for the help though. I learned a lot.

Tags for this Thread

Posting Permissions

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