Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    8

    Unanswered: Updating from two tables with large data

    How to do bulk upload and delete

    There are two tables

    Code:
    TX 
    ------------------------------
    HIT_ID  USERID AUTHENTICATED
    Code:
    USERACCESS 
    --------------------------------
    HIT_ID USERID
    I Want to update all the rows from USERACCESS to
    TX such that USERID is updated in TX table where
    TX.HIT_ID equals USERACCESS.HIT_ID


    I have used following query:
    Code:
    update 
        TX 
    set 
       USERNAME = (select USERID from USERACCESS where 
    USERACCESS.HIT_ID = TX.HIT_ID FETCH FIRST 1 ROW ONLY) ,
       ISAUTHENTICATED = 'Y'
    WHERE 
       HIT_ID IN (
              select WEBHIT_ID FROM USERACCESS.HIT_ID 
       )
    There crores of records in TX table and lacks of records in USERACCESS TABLE. Please suggest the update query I am running is ok or not. It is taking long time to update.

    I have to delete the HIT_ID from USERACCESS table that are updated to TX table. Please suggest how can I achieve this tasks.

    For now I have written a trigger that that does delete on Update of TX table.

    Any help appreciated.
    Last edited by prashant.gadekar; 09-20-11 at 04:09. Reason: Coulmn name mistakes

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought there were many use of incorrect column names.

    For example:
    TX.WEBHIT_ID equals USERACCESS.HIT_ID
    select WEBHIT_ID FROM ...
    they may be TX.HIT_ID

    set USERNAME = ...
    it may be USERID

    USERACCESS.HIT_ID = TX.USER_ID
    it may be
    USERACCESS.HIT_ID = TX.HIT_ID
    or
    USERACCESS.USERID = TX.USERID
    ?

    ISAUTHENTICATED = 'Y'
    it may be AUTHENTICATED

    FROM USERACCESS.HIT_ID
    may be syntax error??


    Anyhow, here is my idea including some guesses, but not tested.
    Code:
    WITH
     update_tx AS (
    SELECT hit_id
     FROM  FINAL TABLE
           (UPDATE tx
             SET   userid
                 = (SELECT userid
                     FROM  useraccess ua
                     WHERE ua.hit_id = tx.hit_id
                   )
                 , authenticated
                 = 'Y'
             WHERE EXISTS
                   (SELECT 0
                     FROM  useraccess ua
                     WHERE ua.hit_id = tx.hit_id
                   )
           ) tx
    )
    SELECT COUNT(*) rows_deleted
     FROM  OLD TABLE
           (DELETE FROM
                   useraccess ua
             WHERE ua.hit_id
                   IN (SELECT hit_id
                        FROM  update_tx
                      )
           ) ua
    ;
    Last edited by tonkuma; 09-20-11 at 05:12. Reason: Move authenticated to SET clause. Replace from "FINAL" to "OLD" in "FROM FINAL TABLE (DELETE "

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Yes, too much incorrect info.

    To reduce the number of rows updated per transaction (instead of crores and lacks(sic))

    Code:
    FROM  FINAL TABLE
           (UPDATE (select * from tx where hit_id between <a> and <b>) tx
               SET userid
    the between clause should restrict the number of records in tx that will be updated.

    hth
    Visit the new-look IDUG Website , register to gain access to the excellent content.

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
  •