CODEWITHSUNDEEP
sqloracle10g
jeeperscreepers
jeeperscreepers

Reputation: 143

Update multiple rows on second table based on first table data

I have 2 tables

TBL_HEADER
----------
HEADER_ID
COST_CENTER


TBL_RESULTS   
-----------
WEEK_NO
COST_CENTER
HEADER_ID_FK

I have a requirement to copy all of the COST_CENTER_CODES from TBL_HEADER into TBL_RESULTS joining on the HEADER_ID > HEADER_ID_FK.

I tried this but the subquery is returning multiple rows

  UPDATE
   TBL_RESULTS R
SET
  COST_CENTRE = (
  SELECT 
    H.COST_CENTRE
  FROM
    TBL_HEADER H,
    TBL_RESULTS R
  WHERE
    H.HEADER_ID = R.HEADER_ID_FK
  )

Can someone point me in the right direction and explain why this is happening?

I'm using Oracle 10.2.0.4

many thanks

JC

Upvotes: 0

Views: 78

Answers (3)

cosmos
cosmos

Reputation: 2303

UPDATE
   TBL_RESULTS R
SET
  COST_CENTRE = (
  SELECT 
    H.COST_CENTRE
  FROM
    TBL_HEADER H
  WHERE
    H.HEADER_ID = R.HEADER_ID_FK
  )

Upvotes: 0

waka
waka

Reputation: 3417

  SELECT 
    H.COST_CENTRE
  FROM
    TBL_HEADER H,
    TBL_RESULTS R
  WHERE
    H.HEADER_ID = R.HEADER_ID_FK

will return every Cost Centre from TBL_HEADER where the IDs are matching. You need to "join" your subselect with your updateting statement, ie "where H.HEADER_ID = TBL_RESULTS.HEADER_ID_FK" where "TBL_RESULTS" is the table from your update statement.

Also, you don't need TBL_RESULTS R in the sub select.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You want a correlated subquery, not a subquery with a join:

UPDATE TBL_RESULTS
SET COST_CENTRE = (SELECT H.COST_CENTRE
                   FROM TBL_HEADER H 
                   WHERE H.HEADER_ID = TBL_RESULTS.HEADER_ID_FK
                  )

The extra reference to tbl_results was causing problems.

Upvotes: 1

Related Questions