OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Update table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Update table [message #22178]

Fri, 27 September 2002 02:26

chhan
Messages: 19
Registered: June 2002

Junior Member

Hi All,

i have two tables called TBLENTERINTO and DIMENTERINTO which is a copy of the TBLENTERINTO table. i have created DIMENTERINTO using the following SQL statement:

Create table dimENTERINTO(
EnterInto number(10) NOT NULL PRIMARY KEY,
VehicleID number(10) NOT NULL REFERENCES DIMVEHICLE(VEHICLEID),
ZoneID number(10) NOT NULL REFERENCES DIMZONE(ZONEID),
FineID number(10) NOT NULL REFERENCES DIMFine(fineID),
DateIn Date,
TimeIn varchar2(5)
);

I have inserted data in DIMENTERINTO table using the following:

insert into dimEnterInto
(EnterInto,
VehicleID,
ZoneID,
FineID,
DateIn,
TimeIn)
select EnterInto, VehicleID, ZoneID, FineID, DateIn, TimeIn from tblEnterInto;

The above work ok, but then i went back into TBLENTERINTO table and inserted more records(about 100), i now want to update DIMENTERINTO table.

How do i do this without delete the existing data in DIMENTERINTO??

Thanks in advance.

Chhan

Report message to a moderator

Re: Update table [message #22179 is a reply to message #22178]

Fri, 27 September 2002 02:32

Skumar
Messages: 17
Registered: April 2002

Junior Member

I am sure there can be other better way . What came to my mind is this.

insert into dimEnterInto
(EnterInto,
VehicleID,
ZoneID,
FineID,
DateIn,
TimeIn)
select a.EnterInto, a.VehicleID, a.ZoneID, a.FineID, a.DateIn, a.TimeIn from tblEnterInto a
where a.EnterInto not in (select b.EnterInto from dimEnterInto b);

Report message to a moderator

Re: Update table [message #22190 is a reply to message #22178]

Fri, 27 September 2002 09:13

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

I would recommend a NOT EXISTS check rather than NOT IN - this should perform better due to the index on enterinto:

insert into dimenterinto
  select * from tblenterinto t
   where not exists (select null
                       from dimenterinto d
                      where d.enterinto = t.enterinto);

Report message to a moderator

Previous Topic:	WHERE CLAUSE
Next Topic:	How do I Submit a Table Name as a Parameter to a Procedure for Processing?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 28 20:57:17 CDT 2024