UPDATE... FROM Statement???

[sndkick]

I have seen SQL syntax for an UPDATE...FROM before (I'm pretty sure) but can't seem to get it to work in Oracle. I have a Master table that I need to update with all of the matching information from a NewInfo table. Here's a sample of what i'm trying to do.

MASTER
id (number)
name (varchar)
company (varchar)
...and other fields.

NEWINFO
id (number)
name (varchar)
company(varchar)

I am looking for a statement like this:

UPDATE master
SET name = ni.name,
company = ni.company
FROM newinfo ni
WHERE id = ni.id

I need to update about 8000 rows and would like to do it all in a statement like the above. The SET ### = (SELECT..FROM..WHERE..) doesn't work with multiple records, so I'm looking for a way around this.

Actually I have about 4 more tables that I need to grab info from to update the records in this master table. The data is being imported from external sources first. That's why they're all in different tables.

The only other way I could think is to grab all IDs from one table and put them in an array, then loop through all of these IDs. I would think the above statement would be faster, but it doesn't seem to work. Can anyone tell me why it doesn't work, or the proper way to accomplish something like this?

Thanks a lot.

 

[carp]

Somebody will probably have the simple answer, but it's just not coming to me right now. In the meantime, how about a slightly more involved approach that is STILL easier than 8000 update statements:

DECLARE
CURSOR ni_cursor IS SELECT * FROM new_info;
BEGIN
FOR i IN ni_cursor LOOP
UPDATE master SET name = i.name, company = i.company
WHERE id = i.id;
END LOOP;
COMMIT;
END;

 

[uparna]

HI,
i think that carp's suggestion is the best. As far i know , joins using aliases is not allowed in the update statements . It is always good if you use a plsql for the same . The PL SQL also involves a better chance of traceability.

Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .

 

[sem]

UPDATE master
SET (name,company) =
(select ni.name, ni.company
FROM newinfo ni WHERE id = ni.id)

This will work if subquery returns 1 record. Otherwise an error will be raised if more then 1 record returned(you may limit it by dummy "where rownum=1" condition) or values will be nullified if no matching rows found.

Though pl/sql provides better control it also decreases speed significantly. But for 8000 rows it normally does not matter.