CODEWITHSUNDEEP
sqloracle-database
Elliot B
Elliot B

Reputation: 20

Update field where the items are part of a subquery

I am testing some software and need to make some adjustments to the fields manually. For all items that are produced at factory A, the lead time needs to be adjusted for the other factories those items are produced. However, the other items that are at the other factories need the normal leadtime.

I have the query to select the items that are produced at the alternate factories. I've tried using update where exists and having that be a subquery. I can't seem to get it to work as I feel it should

  update newgdmoperation
  set newgdmoperation.productionoffset = 75
  where exists
(
  select
newgdmoperation.operationid
from newgdmoperation
right join
(
  select mainproductid,productionoffset
  from newgdmoperation
  where fromlocationid = 'KR'
  and transporttype like 'Ves%'
) a
on newgdmoperation.mainproductid = a.mainproductid
where fromlocationid <> 'KR'
and transporttype like 'Ves%'
)

This doesn't give any error results. However, it updates the field for all item. The subquery under the where clause does in fact return the operationid (unique id) for the items that need to be updated. I was expecting that with the where exists, that only the items in the subquery would be updated while the rest would be left untouched.

Upvotes: 0

Views: 27

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Assuming that you're trying to update the NEWGDMOPERATION table it looks to me like you should use IN rather than EXISTS, and so your statement should be

UPDATE NEWGDMOPERATION g
  SET g.PRODUCTIONOFFSET = 75
  WHERE g.OPERATIONID IN (SELECT g2.OPERATIONID
                            FROM NEWGDMOPERATION g2
                            RIGHT JOIN (SELECT g3.MAINPRODUCTID,
                                               g3.PRODUCTIONOFFSET
                                          FROM NEWGDMOPERATION g3
                                          WHERE g3.FROMLOCATIONID = 'KR' AND
                                                g3.TRANSPORTTYPE LIKE 'VES%') a
                              ON g2.MAINPRODUCTID = a.MAINPRODUCTID
                            WHERE g2.FROMLOCATIONID <> 'KR' AND
                                  g2.TRANSPORTTYPE LIKE 'VES%')

Upvotes: 1

Related Questions