Myles
Yes, that's pretty tidy - I'm looking forward to the transition.
In the meantime I'm favouring join and BatchMove which I find pretty snappy.
Tks,
Mike
----- Original Message -----
From: "Myles Penlington" <[EMAIL PROTECTED]>
To: "Multiple recipients of list database" <[EMAIL PROTECTED]>
Sent: Wednesday, February 23, 2000 5:17 PM
Subject: RE: [DUG-DB]: Basic SQL Question - UPDATE
> Mike,
>
> Here is an example from Intterbase, using a stored procedure - part of a
one
> off conversion.
>
> set term !! ;
>
> create procedure UpdateEmployees
> as
> declare variable EmployeeID integer;
> declare variable ApppointmentID integer;
> declare variable EmployeeAppointmentPK integer;
> begin
> for select EA.EmployeeID, EA.AppointmentID, EA.EmployeeAppointmentPK
from
> EmployeeAppointments EA, EmployeePayments ER
> where ER.EmployeeAppointmentPK = EA.EmployeeAppointmentPK
> into :EmployeeID, :ApppointmentID, :EmployeeAppointmentPK
> do begin
> update EmployeePayments set
> EmployeeID = :EmployeeID,
> AppointmentID = :ApppointmentID
> where EmployeeAppointmentPK = :EmployeeAppointmentPK;
> end
> exit;
> end !!
>
> set term ; !!
>
> execute procedure UpdateEmployees;
>
> drop procedure UpdateEmployees;
>
>
>
> > -----Original Message-----
> > From: Mike Osborne [SMTP:[EMAIL PROTECTED]]
> > Sent: Wednesday, 23 February 2000 17:06
> > To: Multiple recipients of list database
> > Subject: [DUG-DB]: Basic SQL Question - UPDATE
> >
> > I would like to update some columns of specific rows in a table
> > (TblTarget) with values from a specific row from another table
> > (TblSource).
> >
> > Is there a way that this can be done in a single SQL statement (and more
> > concise than what follows)?
> >
> > It looks like
> >
> > UPDATE TblTarget set
> > col2 = (select col2 from TblSource where ...),
> > col3 = (select col3 from TblSource where ...),
> > col5 = (select col5 from TblSource where ...)
> > where
> > col1 = some_value
> >
> > is possible but seems very long-winded expecially since the "select's"
all
> > test for the same condition and I'm wanting to deal to 9 columns with 3
> > compares in each select "where" subclause.
> >
> > Other options are select from TblSource first and apply the result as
> > parameters for each "set" (col2 = :col2, col3 = :col3 ...)
> >
> > Or, join TblTarget & TblSource to give a query of an image of the
updated
> > rows and apply through BatchMove with Mode batUpdate.
> >
> > What is the tidiest/optimal method to do this?
> >
> > Currently using Paradox, Delphi 4, but interested if more/better options
> > available under InterBase as migrating in the near future..
> >
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
