| View previous topic :: View next topic |
| Author |
Message |
Larry
Guest
|
 Posted: Sun Dec 17, 2006 5:29 am Post subject: Updating multiple records from a lookup table |
 |
|
I want to update several thousand records from a lookup table.
The Lookup table has a list of EmpIDs and are related to the main table by
Station Number
Conceptually I want to
Update MainTable
Set EmpID = (Select EmpID from LookUpTable where Station = Station)
Where MyDate = :Date
and Shift = 1
I am not having any success in filtering the lookup table to one EmpID.
They are unique to station. I have tried various subselects and joins but
no good results. I am in a hotel room without my books I normally use as a
resource. Can someone get me started.
Thanks
Larry |
|
| Back to top |
|
 |
Oliver Townshend
Guest
|
| Posted: Sun Dec 17, 2006 5:58 am Post subject: Re: Updating multiple records from a lookup table |
|
|
| Quote: | Conceptually I want to
Update MainTable
Set EmpID = (Select EmpID from LookUpTable where Station = Station)
Where MyDate = :Date
and Shift = 1
|
In SQL Server you would do the following:
update maintable
set empid=lookuptable.empid
from lookuptable
where maintable.station=lookuptable.station
and mydate=:date
and shift=1
In Oracle I'd do it like this
Update MainTable
Set EmpID = (Select EmpID from LookUpTable where LookupTable.Station =
MaintTable.Station)
Where MyDate = :Date
and Shift = 1
and exists (Select EmpID from LookUpTable where LookupTable.Station =
MaintTable.Station)
But I'd also seriously consider doing it in a script using a loop.
Oliver Townshend |
|
| Back to top |
|
|
Larry
Guest
|
| Posted: Sun Dec 17, 2006 9:11 am Post subject: Re: Updating multiple records from a lookup table |
|
|
Thanks
"Oliver Townshend" <oliveratzipdotcomdotau> wrote in message
news:458487f0 (AT) newsgroups (DOT) borland.com...
| Quote: | Conceptually I want to
Update MainTable
Set EmpID = (Select EmpID from LookUpTable where Station = Station)
Where MyDate = :Date
and Shift = 1
In SQL Server you would do the following:
update maintable
set empid=lookuptable.empid
from lookuptable
where maintable.station=lookuptable.station
and mydate=:date
and shift=1
In Oracle I'd do it like this
Update MainTable
Set EmpID = (Select EmpID from LookUpTable where LookupTable.Station =
MaintTable.Station)
Where MyDate = :Date
and Shift = 1
and exists (Select EmpID from LookUpTable where LookupTable.Station =
MaintTable.Station)
But I'd also seriously consider doing it in a script using a loop.
Oliver Townshend
|
|
|
| Back to top |
|
|
Luiz Vaz
Guest
|
| Posted: Mon Dec 18, 2006 9:11 am Post subject: Re: Updating multiple records from a lookup table |
|
|
Hi Larry,
This Oracle SQL is easy than you used:
Update (Select A.EmpID EmpID_A
from LookUpTable A,
MaintTable B
where A.Station = B.Station
and A.MyDate = :Date
and A.Shift = 1)
Set EmpID_A = EmpID_B
You can see Tom talking about this here:
http://asktom.oracle.com/pls/ask/f?p=4950:8:9292996848109287531::NO:::
Best regards,
Luiz vaz |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
