Re: sql statement for update operation needed

Thank you Valentine,

your proposal worked. And I agree with your statements that this should not be necessary in a real world database.
The things is that I migrate data from a huge excel worksheet into an Oracle database and so have to create
keys, indexes and so on manually. I have to do it with my status of Oracle knowhow.
Probably there are ways to do this in a more elegant manner. But these are beyond my horizon right now.

Regards,
Sönke

"Valentine Gogichashvili" <valgog_at_biofrontera.de> schrieb im Newsbeitrag news:3e6cae9f_at_news.piro.net...
> 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 Thu Mar 13 2003 - 06:50:27 CST