Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

sql update QUESTIONS

JUAN C RIVERA
Occasional Visitor

sql update QUESTIONS

HOW CAN I MAKE AN UPDATE TO A FIELD IN A TABLE WITH DATA FROM A FIELD FROM ANOTHER TABLE AND BOTH TABLES HAVE A SIMILAR FIELD THAT I CAN USE TO RELATE THE DATA THAT I WANT TO UPDATE.

I HAD TRIED THIS:
update uid_info set routing_id = (select a.routing_id from routing a , uid_info b where a.PART_NUMBER = b.PART_NUMBER
and a.DEFAULT_ROUTING='1')
5 REPLIES
Fred Ruffet
Honored Contributor

Re: sql update QUESTIONS

update uid_info b
set routing_id = (
select a.routing_id
from routing a
where a.PART_NUMBER = b.PART_NUMBER
and a.DEFAULT_ROUTING='1');

regards,

Fred
--

"Reality is just a point of view." (P. K. D.)
Brian Crabtree
Honored Contributor

Re: sql update QUESTIONS

Juan,

Fred has a good start, however you need to put a where clause on the update as well. I don't know enough about what you are trying to do, however this is an example of what I mean:

update users a set number =
(select number from phonelist b where a.username = b.username)
where exists
(select 1 from phonelistb where a.username = b.username);

Otherwise, you will repeatedly update the routing_id from the routing table for each row of the uid_info table.

Thanks,

Brian
Yogeeraj_1
Honored Contributor

Re: sql update QUESTIONS

hi juan,

an alternative example using PL/SQL will be:
begin
for c1 in (select routing_id, uid_info from routing) loop
update uid_info
set routing_id = c1.routing_id,
uid_info = c1.uid_info;
where part_number= c1.part_number
and default_routing='1';

--dbms_output.put_line('updating '||c1.part_number);
end loop;
commit;
end;

hope this helps too!
(Note that doing it in a single SQL statement will be the best option, especially if the table has thousands of records)

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: sql update QUESTIONS

hi juan,

an alternative example using PL/SQL will be:
begin
for c1 in (select routing_id, uid_info from routing) loop
update uid_info
set routing_id = c1.routing_id,
uid_info = c1.uid_info
where part_number= c1.part_number
and default_routing='1';

--dbms_output.put_line('updating '||c1.part_number);
end loop;
commit;
end;

hope this helps too!
(Note that doing it in a single SQL statement will be the best option, especially if the table has thousands of records)

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sanjay Kumar Suri
Honored Contributor

Re: sql update QUESTIONS

Oracle 9i Merge statement makes all this very simple as the following example shows:

MERGE INTO SALES_FACT D
USING SALES_JUL01 S
ON (D.TIME_ID = S.TIME_ID
AND D.STORE_ID = S.STORE_ID
AND D.REGION_ID = S.REGION_ID)
WHEN MATCHED THEN
UPDATE
SET d_parts = d_parts + s_parts,
d_sales_amt = d_sales_amt + s_sales_amt,
d_tax_amt = d_tax_amt + s_tax_amt,
d_discount = d_discount + s_discount
WHEN NOT MATCHED THEN
INSERT (D.TIME_ID ,D.STORE_ID ,D.REGION_ID,
D.PARTS ,D.SALES_AMT ,D.TAX_AMT ,D.DISCOUNT)
VALUES (
S.TIME_ID ,S.STORE_ID ,S.REGION_ID,
S.PARTS ,S.SALES_AMT ,S.TAX_AMT ,S.DISCOUNT);

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.