Hi,

Is this best way to right this query

update bigtable BT set qty = qty +
(select tm.tmpqty from tmptable tm where tm.col1 = BT.col1 and
tm.col2 = BT.col2 ) where BT.col1 || BT.col2 in (select tm1.col1 || tm1.col2 from tmptable tm1)

bigtable : has 2.5 mil records and growing indexed o col1 and col2
Col1 Col2 Qty
A A1 10
A A2 11
B A1 12
B A2 13

tmptable : may have 100 records
Col1 Col2 Qty
A A1 1
A A2 2
B A1 10

Result expected
bigtable :
Col1 Col2 Qty
A A1 10+1
A A2 11+2
B A1 12+10
B A2 13

I am worried that my query will be slow because of || condition in where clause and i have indexes only on individual columns in big table. To simplify query above i only used col1 and col2 actually i will have 5 columns to concat.

Thanks in advance

This would work.

WHERE (BT.col1, BT.col2) IN (SELECT tm1.col1, tm1.col2 FROM tmptable tm1)

Thanks it worked, One more question

In above example I need to update qty if the col1 and col2 exists else insert
i am planning to these 2 steps

update bigtable BT set qty = qty +
(select tm.tmpqty from tmptable tm where tm.col1 = BT.col1 and
tm.col2 = BT.col2 ) where (BT.col1 , BT.col2) in (select tm1.col1,tm1.col2 from tmptable tm1)

>> Will update any matches

Insert into bigtable BT (BT.col1, BT.col2, BT.qty) (select tm.col1, tm.col2, tm.qty from tmptable tm where (tm.col1 , tm.col2) not in (select BT1.col1,BT1.col2 from bigtable BT1 )

>> Will insert non matches

Does this look good?

Regards

You may want to use a MERGE statement to combile the UPDATE statement and the INSERT statement.

Thanks again tonkuma