Update columns in one table with columns from another

ducati321 · #1 (**permalink**) 07-14-11, 14:53

I've been googling for the last hour, and found some things that I thought would work, but have not...

I have table "revisions", and table "inventory".
Both have a column "revision".

I want to update revisions.revision column with inventory.revision, where
revision.site = inventory.site AND revision.item_no = inventory.item_no

what i've tried and hasn't worked...

UPDATE revisions
SET revisions.revision = i.revision
FROM revisions, inventory i
WHERE revisions.site = inventory.site and revisions.item_no = inventory.item_no

oracle gives error, sql not properly ended

thanks folks!

anacedent · #2 (**permalink**) 07-14-11, 15:52

It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

Littlefoot · #3 (**permalink**) 07-15-11, 01:47

Something like this might do the job:

Code:

update revisions r set
  r.revision = (select i.revision
                from inventory i
                where i.site = r.site
                  and i.item_no = r.item_no
                )

Additional WHERE clause might be needed to restrict which records should be updated.

ducati321 · #4 (**permalink**) 07-15-11, 06:56

This was exactly correct. Oddly enough, I tried this earlier and got an error... but when I tried it again, I realized I had made a mistake and not included a clause that would narrow down the inventory table to a single record and it was returning multiple rows, which wouldn't allow the update to happen.

Thanks for the help!

Quote:

Originally Posted by Littlefoot

Something like this might do the job:

Code:

update revisions r set
  r.revision = (select i.revision
                from inventory i
                where i.site = r.site
                  and i.item_no = r.item_no
                )

Additional WHERE clause might be needed to restrict which records should be updated.

Littlefoot · #5 (**permalink**) 07-15-11, 14:36

Quote:

I tried this earlier and got an error

That's why it is important to copy/paste your own SQL*Plus session. Doing so, we'd see what you did and how Oracle responded. Providing assistance is much easier in such a case.

Internet Services

Web Development

Digital Marketing

Consumer Tech