CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-update
name_masked
name_masked

Reputation: 9794

Update Query fails to update correctly - Oracle

Following up to my earlier query Update Query, I went ahead and rewrote the entire query as follows:

Table A is connected to Table B by one to many relation on [PRD_ID, PRD_VER, PY_ID, TPY_ID] and Table C is connected to Table B by one to one relation on [PRD_GTIN_ID]. I need to copy columns X, Y, Z on Table A to X, Y, Z on Table C

DECLARE CURSOR eachRec IS 
SELECT  cgl.prd_gtin_id,
        tc.PRD_SPL_OR_QTY_MULTIPLE
FROM B cgl,
     A tc
WHERE cgl.prd_id       = tc.prd_id
  AND cgl.prd_ver      = tc.prd_ver
  AND cgl.py_id        = tc.py_id
  AND cgl.tpy_id       = tc.tpy_id;

BEGIN
  FOR EACH_ROW IN eachRec
  LOOP
    UPDATE C
    SET    C.PRD_ORDER_QTY_MULTIPLE=EACH_ROW.PRD_ORDER_QTY_MULTIPLE
    WHERE  C.prd_gtin_id = EACH_ROW.prd_gtin_id;
  END LOOP;
END;

The query in addition to updating the right matches, is also updating random rows which is incorrect. If anyone can help me find the issue with the query I have written?

Upvotes: 1

Views: 988

Answers (1)

Mark J. Bobak
Mark J. Bobak

Reputation: 14385

What you really need here is to understand the concepts of a key-preserved table and updatable join view.

As these are non-trivial subjects, and Oracle does a good job covering them in the documentation, I'll just point you to the relevant documentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e25494/views001.htm#ADMIN11782

If you can read and understand those concepts, it shouldn't be a problem to do what you want with a single update statement, which will be both clearer and easier to understand, as well as much faster and more efficient.

Hope that helps.

Upvotes: 1

Related Questions