problem in update statement

[prakashs43]

Hi ,

i have two tables grp and grp_bkp ,

I would like to update grp table particular column for a particular selection of rows from grp_bkp .

but when i try to update table is getting locked .

please let me know if i am missing anything .

update grp a
set cdng_value = ( select cdng_value from grp_bkp b
where a.grp_id = b.grp_id
and a.version_id = 12517
and b.version = 12517 ) ;

Grp_id is primary column .

please let me know what i am missing .

[abhaysk]

you should add a exists condition else all the rows will be updated with null for non matching conditions

Better use Merge Statement.. Its much faster..

Abhay.

[marist89]

Originally posted by prakashs43
Hi ,

i have two tables grp and grp_bkp ,

I would like to update grp table particular column for a particular selection of rows from grp_bkp .

but when i try to update table is getting locked .

please let me know if i am missing anything .

update grp a
set cdng_value = ( select cdng_value from grp_bkp b
where a.grp_id = b.grp_id
and a.version_id = 12517
and b.version = 12517 ) ;

Grp_id is primary column .

please let me know what i am missing .

You're updating the whole table. You need a WHERE clause for your UPDATE statement.

[Dilippatel]

Prakash,

Can you give more inputs,

1. Your query hangs or come out with "some records updated"

2. Is some relation between those tables. Any Foreigh keys ??



Parsonal Query: Are you S.Prakash (SGU MCA 95) ?

[abhaysk]

Originally posted by Dilippatel
Parsonal Query: Are you S.Prakash (SGU MCA 95) ?

look his profile.. does his b'day sounds so ..

Abhay.

[prakashs43]

Hi ,

My Query is hanging , it seems it is doing something internally but taking very long time .
so far not even a single record ha supdated .
i have 10,000 records to update .
this table has 100,000 records and backup table has 300,000 records

grp table has several foreign keys .
and 1 primary key on grp_id column

[Dilippatel]

You are missing where clause with update statement. It should reduce the no records to update, and so to execution time.

[prakashs43]

I tried like this but still this is taking lot of time ....

update grp a
set cdng_value = ( select cdng_value from grp_bkp b
where a.grp_id = b.grp_id
and a.version_id = b.version_id
and b.version = 12517 )
where a.version_id = 12517 ;

explain plan for above statement is

UPDATE STATEMENT Optimizer=CHOOSE (Cost=52 Card=2190 Bytes=26280)
UPDATE OF GRP
TABLE ACCESS (BY INDEX ROWID) OF GRP (Cost=52 Card=2190 Bytes=26280)
INDEX (RANGE SCAN) OF XIF368GRP (NON-UNIQUE) (Cost=9 Card=2190)
FILTER
TABLE ACCESS (FULL) OF GRP_070904 (Cost=445 Card=32 Bytes=1216)


COULD YOU PLEASE TELL HOW TO USE EXIST CLAUSE ...

[prakashs43]

I am trying using exists clause .
is there any problem , even this query it is taking time ..
no error message ..

UPDATE grp a
SET cdng_quest =
(SELECT cdng_quest
FROM grp_070904 b
WHERE a.grp_id = b.grp_id
AND a.version_id = b.version_id
AND a.version_id = 27517)
WHERE EXISTS (SELECT 'X'
FROM grp_070904 b
WHERE a.grp_id = b.grp_id
AND a.version_id = b.version_id
AND a.version_id = 27517 )
and a.version_id = 27517 ;

[marist89]

Why would there be an error message? If I told you my car was not fast enough what would be the solution? You need to read http://www.dbasupport.com/forums/sho...threadid=36219 before blindly posting questions that we have no hope in answering.