Home » SQL & PL/SQL » SQL & PL/SQL » How to UPDATE a table A base on table B condition matched with table A?
How to UPDATE a table A base on table B condition matched with table A? [message #252538] Thu, 19 July 2007 02:30 Go to next message
atgl
Messages: 25
Registered: March 2006
Junior Member
I have 2 table which is SETIA_CE_MASTER_JOB and SETIA_CE_EVAL_JOB.

I want to update a column named "GCD_STATUS" in SETIA_CE_EVAL_JOB if the row matches with SETIA_CE_MASTER_JOB.

My idea of query is something like this:-

Quote:
UPDATE SETIA_CE_EVAL_JOB j, SETIA_CE_MASTER_JOB m
SET j.GCD_STATUS = 'Y'
where j.eval_number = 0
and j.eval_number = m.eval_number
and j.contractor_id = m.contractor_id
and j.contractor_id = 87
and j.project_id = m.project_id
and m.business_unit_id = 11


Actually my update very dependent of m.business_unit_id from table SETIA_CE_MASTER_JOB to update j.GCD_STATUS in SETIA_CE_EVAL_JOB.

I am using oracle 8i.
Illustration before updates
Quote:

SETIA_CE_EVAL_JOB
eval_num project_id contractor_id gcd_status
0 1 87 N
0 2 87 N
0 3 87 N
SETIA_CE_MASTER_JOB
eval_num project_id contractor_id business_unit_id
0 1 87 11
0 2 87 11
0 3 87 3



Re: How to UPDATE a table A base on table B condition matched with table A? [message #252540 is a reply to message #252538] Thu, 19 July 2007 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Update (select table keys, GCD_STATUS from the_tables where join condition)
set GCD_STATUS = 'Y'
/

Regards
Michel
Re: How to UPDATE a table A base on table B condition matched with table A? [message #252570 is a reply to message #252540] Thu, 19 July 2007 03:27 Go to previous messageGo to next message
atgl
Messages: 25
Registered: March 2006
Junior Member
I m running as solution provided:-

Update (
select m.eval_number, m.project_id, m.contractor_id, m.business_unit_id, j.gcd_status
from SETIA_CE_MASTER_JOB m
, SETIA_CE_EVAL_JOB j
where j.eval_number = 0
and j.eval_number = m.eval_number
and j.contractor_id = 233
and j.contractor_id = m.contractor_id
and j.project_id = m.project_id
and m.business_unit_id = 11
)
set GCD_STATUS = 'Y'

With only the select query, I am able to get the rows I wish to update but when I added the Update clause, then the error below listed:-

ORA-01779: cannot modify a column which maps to a non key-preserved table

FYI, I'm using TOAD and in oracle8i env.
I have other column which is not related for joining condition in both tables
Re: How to UPDATE a table A base on table B condition matched with table A? [message #252573 is a reply to message #252570] Thu, 19 July 2007 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you format your query this time?

What are the primary key of your tables? (real one declared in Oracle not logical one)

Regards
Michel

Re: How to UPDATE a table A base on table B condition matched with table A? [message #252578 is a reply to message #252573] Thu, 19 July 2007 04:04 Go to previous messageGo to next message
atgl
Messages: 25
Registered: March 2006
Junior Member

I think i did not define the primary in oracle. How do i do that?
Re: How to UPDATE a table A base on table B condition matched with table A? [message #252583 is a reply to message #252538] Thu, 19 July 2007 04:52 Go to previous messageGo to next message
atgl
Messages: 25
Registered: March 2006
Junior Member
Hi Michel,
I have added the primary key constraints but I still get the same error. Please advice.

Thank you.
Re: How to UPDATE a table A base on table B condition matched with table A? [message #252585 is a reply to message #252583] Thu, 19 July 2007 05:06 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same question: what are they? On which columns for each table?

Regards
Michel
Previous Topic: Inner Join - foreign keys are in a range
Next Topic: ORA-01843: not a valid month Error
Goto Forum:
  


Current Time: Sat Dec 10 10:31:28 CST 2016

Total time taken to generate the page: 0.13708 seconds