Updating from two tables with large data

prashant.gadekar · #1 (**permalink**) 09-19-11, 11:39

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.

tonkuma · #2 (**permalink**) 09-19-11, 21:45

I thought there were many use of incorrect column names.

For example:

Quote:

TX.WEBHIT_ID equals USERACCESS.HIT_ID

Quote:

select WEBHIT_ID FROM ...

they may be TX.HIT_ID

Quote:

set USERNAME = ...

it may be USERID

Quote:

USERACCESS.HIT_ID = TX.USER_ID

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

Quote:

ISAUTHENTICATED = 'Y'

it may be AUTHENTICATED

Quote:

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
;

sathyaram_s · #3 (**permalink**) 09-20-11, 04:11

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

Internet Services

Web Development

Digital Marketing

Consumer Tech