sql update QUESTIONS

[JUAN C RIVERA]

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')

[Fred Ruffet]

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

[Brian Crabtree]

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]

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

[Yogeeraj_1]

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

[Sanjay Kumar Suri]

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