Oracle update Query does not work

Question

I have an ORACLE query which does not work for this query.

UPDATE emp a set a.Enq_Status = 'PROGRESS' 
where exists (select * from emp  a, Data b   
         WHERE a.SA_Enq_Status is NULL 
           and a.EQ_Status = 'ACTIVATED' 
           and a.Activation_Return_Code = 0 
           and a.Alert_Code > 0 
           and a.User_Id = b.User_Id 
           and (b.Is_Associate is NULL or b.Is_Associate = 0)  
           and (b.Stk_Schd is NULL)
           and (b.Stk_Dis_Amt is NULL)
);

My Problem is I want to update mutiple records in the table emp (A) to status to 'PROGRESS'.But when executing this query all records in a.Enq_status is changes.Please help me in this.Updation is not correct.Please help me in this

What are the values in a and b when a wrong update occurs?

mmmmmm
– mmmmmm

2011-08-30 12:48:32 +00:00
Commented Aug 30, 2011 at 12:48 — mmmmmm
– mmmmmm, Commented Aug 30, 2011 at 12:48

Ollie · Accepted Answer · 2011-08-30 12:49:00Z

5

You have specified table emp both in the update query and the subquery too, Oracle will treat these as seperate tables. You need a correlated subquery:

UPDATE emp a
   set a.Enq_Status = 'PROGRESS'   
 where exists (select 'X'                   
                 from Data b                   
                WHERE a.SA_Enq_Status is NULL                    
                  and a.EQ_Status = 'ACTIVATED'                    
                  and a.Activation_Return_Code = 0                    
                  and a.Alert_Code > 0                    
                  and a.User_Id = b.User_Id                    
                  and (b.Is_Associate is NULL or b.Is_Associate = 0) 
                  and (b.Stk_Schd is NULL)
                  and (b.Stk_Dis_Amt is NULL));

You could probably just update the emp table without the need for the subquery though if you link to the Data table in the where clause...

answered Aug 30, 2011 at 12:49

Ollie

17.6k8 gold badges50 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user472625 Over a year ago

@peter:What u said is correct.But I have tried the same.The updation works.But all rows in emp a are updated.To state it clearly all rows in emp a which does not match the conditon in where exist clause should remain unchanged.But they are affected.

Jim Hudson Over a year ago

I'd pull out the a-only where clause conditions so it looks like "where a.SA_End_Status is null and a.EQ_Status = 'ACTIVATED' and a.ActivationReturnCode = 0 and a.Alert_Code > 0 and exists (select 'x' from Data b where a.User_Id = b.User_Id... Should not affect the results, but it would be cleaner.

Ollie Over a year ago

@Jim, just done that in a new answer, it makes it a bit clearer what the OP is then trying to do.

CristiC · Accepted Answer · 2011-08-30 12:47:05Z

3

Your update statement will update all the records in emp table because you don't specify the records to update. If your sub-query returns at least one row, the all the emp records will be updated. If it returns no rows, then none of the records will be updated.

Change your update like this:

UPDATE emp SET Enq_Status = 'PROGRESS' 
WHERE id in 
(SELECT a.id 
 FROM emp  a, Data b  
 WHERE a.SA_Enq_Status is NULL and a.EQ_Status = 'ACTIVATED' 
   and a.Activation_Return_Code = 0 and a.Alert_Code > 0 
   and a.User_Id = b.User_Id and (b.Is_Associate is NULL or b.Is_Associate = 0)
   and (b.Stk_Schd is NULL)and (b.Stk_Dis_Amt is NULL)
);

answered Aug 30, 2011 at 12:47

CristiC

22.7k13 gold badges62 silver badges89 bronze badges

2 Comments

user472625 Over a year ago

@peter:where id in(select)..Why we are using the id field...I dont have such a field...Tell me the use of it,so that i could understand

CristiC Over a year ago

I assumed that you have an id field. The idea is to use the primary key of the emp table. Change in the query, instead of id, put your primary key.

Ollie · Accepted Answer · 2011-08-30 13:05:38Z

3

Try:

UPDATE emp a
   SET a.enq_status = 'PROGRESS'
 WHERE a.sa_enq_status IS NULL
   AND a.eq_status = 'ACTIVATED'
   AND a.activation_return_code = 0
   AND a.alert_code > 0
   AND EXISTS
       (SELECT 'X'
          FROM data b
         WHERE a.user_id = b.user_id
           AND ( b.is_associate IS NULL OR b.is_associate = 0 )
           AND b.stk_schd IS NULL
           AND b.stk_dis_amt IS NULL);

answered Aug 30, 2011 at 13:05

Ollie

17.6k8 gold badges50 silver badges61 bronze badges

Collectives™ on Stack Overflow

Oracle update Query does not work

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related