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
