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..
