CODEWITHSUNDEEP
sqloracle-databaseoracle10g
raka
raka

Reputation: 133

Update Query Throwing error

I have to tables T1 and T2. I want to update fourth coloumn in T2 from T1. The query is

update t1 
set   t1.price=(select price from (select t1.price 
                            from t2 
                            where t1.customer=t2.customer and t1.sku=t2.sku and 
                                  t1.weekno= t2.weekno) where rownum=1)

But it is throwing error invalid identifier t1.weekno. I tried couple of other ways but every time I am facing the same problem. I would be grateful to you if you can help me. Thank you

P.S : We are dealing with 5Million records.

Upvotes: 0

Views: 74

Answers (2)

sgeddes
sgeddes

Reputation: 62831

The problem with your query is you're referencing t1.price in your select statement in your correlated subquery. There are a couple ways to resolve this.

One option is to use MERGE for this:

merge
into t1
using   (
        select  t1.customer as updatedCustomer, t2.sku updatedsku, t2.weekno updatedweekno, t2.price updatedPrice
        from    t1
            join    t2
                on      t1.customer = t2.customer and t1.sku=t2.sku and 
                              t1.weekno= t2.weekno
        )
on      (customer = updatedCustomer and sku = updatedsku and weekno = updatedweekno)
when matched then
update
set     price = updatedPrice;

Alternatively, you could update your correlated subquery to use t2.price:

update t1 
set price = (select t2.price 
             from t2 
             where t1.customer=t2.customer and t1.sku=t2.sku and 
                              t1.weekno= t2.weekno and rownum = 1)

Upvotes: 1

Lefteris Bab
Lefteris Bab

Reputation: 787

You need to add the t1 to from clause the subquery :

select t1.price 
                            from t1,t2
                            where t1.customer=t2.customer and t1.sku=t2.sku and 
                                  t1.weekno= t2.weekno

Upvotes: 0

Related Questions