Re: sql statement for update operation needed

Hi, it should not be very difficult if you want to do it only once, to make the tables consistent.

It can be done quite a several ways, one that is most easy but not a beautiful one

update nics:
  set nics.sys_id = (select systems.sys_id from systems where systems.systemname = nics.name)

another way (this will only work if you have primary keys properly assigned) is:
update (select nics.*, systems.sys_id system_sys_id from systems, nics where systems.systemname = nics.name ) system_nic_view  set system_nic_view.sys_id = system_nic_view.system_sys_id

But anyway, this kind of thing should not appear in a real system. Even when you import something from outside (log file for example) you should use triggers and bind the tables with appropiate constraints and primary-foreigh key structures.

Respectfully,

Valentine Gogichashvili

"Sönke Petersen" <sk.petersen_at_gmx.de> wrote in message news:b4i2d2$72q$1_at_news.mch.sbs.de...
> Hello,
>
> I want to update an Oracle table with the contents of a second table.
> The tables look as follows:
>
> systems:
> =====
> sys_id | systemname
> ---------------------
> 123 | meteor
> 124 | komet
> 125 | jupiter
> 126 | saturn
> 127 | venus
> 128 | sirius
>
>
> nics:
> ===
> nic_id | sys_id | name | ip_address
> --------------------------------------------
> | | komet | 192.168.1.2
> | | komet | 192.169.1.1.
> | | jupiter | 10.123.123.1
> | | venus | 10.20.30.40
>
> The table "systems" holds the basic data. Column sys_id of table "nics"
> shall be updated with the appropriate data of table systems.
>
> Can one formulate a SQL statement to do this task?
>
> Regards,
> Sönke
>
>
Received on Mon Mar 10 2003 - 09:23:20 CST