Home » SQL & PL/SQL » SQL & PL/SQL » How to UPDATE a table column from another table value?
How to UPDATE a table column from another table value? [message #249839] Fri, 06 July 2007 02:18 Go to next message
atgl
Messages: 25
Registered: March 2006
Junior Member
Hi,
How do I update a column in table BA depending on data from table A? Example scenario and expected output as display below:-
TABLE A (Eval_no + Project_no = Unique) 
Eval_No  Project_No  Bu_No
1        1            A
1        2            A
1        3            B   

TABLE B (Eval_no + Project_No + Section_No = Unique)
Eval_No  Project_No  Section_No  Bu_No
1        1           1           null
1        1           2           null
1        2           1           null
1        2           2           null
1        2           3           null
1        3           1           null

How to I form my UPDATE QUERY to get TABLE B result as below:-
Eval_No  Project_no  Section_No  Bu_no
1        1           1            A
1        1           2            A
1        2           1            A
1        2           2            A
1        2           3            A
1        3           1            B                    

Re: How to UPDATE a table column from another table value? [message #249841 is a reply to message #249839] Fri, 06 July 2007 02:34 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,
with something like this (untested):
UPDATE (SELECT a.Bu_no new_bu, b.Bu_no
          FROM table_a a INNER JOIN table_b b USING (Eval_no, Project_no))
  SET Bu_no = new_bu;

By the way, have you ever heard about Database normalization?
Why will you not get rid of the column table_b.Bu_no and simply query
SELECT b.Eval_No, b.Project_No, b.Section_No, a.Bu_no
  FROM table_a a INNER JOIN table_b b USING (Eval_no, Project_no)
Re: How to UPDATE a table column from another table value? [message #249844 is a reply to message #249841] Fri, 06 July 2007 02:51 Go to previous messageGo to next message
atgl
Messages: 25
Registered: March 2006
Junior Member
Hi there,

I tried both of your suggqested query but received error of "Command Not Properyly Ended" and both highligted INNER as error. By the way, I am using ORacle 8i & running on TOAD
Re: How to UPDATE a table column from another table value? [message #249847 is a reply to message #249844] Fri, 06 July 2007 02:58 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle 8i does not know join ANSI syntax.
Use Oracle syntax:

FROM a INNER JOIN b USING(col1,col2)

is equivalent to

FROM a,b WHERE a.col1=b.col1 AND a.col2=b.col2

Regards
Michel
Previous Topic: Trigger Problem
Next Topic: how can we find characters in string without using substr?
Goto Forum:
  


Current Time: Sun Dec 11 07:54:19 CST 2016

Total time taken to generate the page: 0.11248 seconds