CODEWITHSUNDEEP
oracle-databasecorrelated-subquery
MidnightThoughtful
MidnightThoughtful

Reputation: 233

Correlated Update query

I can't get the following oracle correlated query to perform updates on just a subsection of records. Currently it updates all records.

I have a table (TBL_REQUESTS) which looks like:

request_id    employee_id    status
------------------------------------
1              1000
1              1001
2              1003
2              1004
2              1005
3              1006

I have a view (VW_STATUS) which looks like:

employee_id   status
---------------------
1000           failed
1001           closed
1002           open
1003           open
1004           close
1005           open
1006           open

I am trying to write an UPDATE statement that would update the status field of employees in TBL_REQUESTS with their corresponding status from VW_STATUS where TBL_REQUESTS.request_id equals a passed value. An extra wrinkle is that there may be more than 1 record per employee in VW_STATUS, however if that poses a problem I can always look for a way to fix that in the VIEW.

This attempt updated every row in TBL_REQUESTS:

UPDATE TBL_REQUESTS r1
SET r1.status =
  (SELECT s.status
   FROM VW_STATUS s
   INNER JOIN TBL_REQUESTS r2 ON s.employee_id = r2.employee_id
   WHERE rd2.request_id = 2)
WHERE EXISTS
    (SELECT s.status
     FROM VW_STATUS s
     INNER JOIN TBL_REQUESTS r2 ON s.employee_id = r2.employee_id
     WHERE rd2.request_id = 2)

Upvotes: 0

Views: 206

Answers (1)

Yaron Idan
Yaron Idan

Reputation: 6765

I think a better way to write this query might be this - 

UPDATE TBL_REQUESTS r1
SET r1.status =
  (SELECT s.status
   FROM VW_STATUS s
   INNER JOIN TBL_REQUESTS r2 ON s.employee_id = r2.employee_id
   WHERE rd2.request_id = 2)
WHERE r1.employee_id in 
    (SELECT s.employee_id
     FROM VW_STATUS s
     INNER JOIN TBL_REQUESTS r2 ON s.employee_id = r2.employee_id
     WHERE rd2.request_id = 2)

This should limit the query to run only on employee_ids that have request_id=2.

Upvotes: 1

Related Questions