Update

IB2007

Table-1
ID       Value
-----------------
AA        1
BB        2
CC       3
DD       4

Table-2
ID        Value
-----------------
AA        1
CC       3
DD       4

i dont have just 2 fields like above, each table
has apprx. 20 fields and 90% fields are same

Is it possible an Update something like below
UPDATE Table-2
SET (SELECT ID,VALUE FROM Table-1
WHERE ID='AA') WHERE ID='AA'

Thank you

"Serra" <no@no.com> wrote in message 
news:477dee51$1@newsgroups.borland.com...
>
> Is it possible an Update something like below
> UPDATE Table-2
> SET (SELECT ID,VALUE FROM Table-1
> WHERE ID='AA') WHERE ID='AA'


No. Fields need to be specified and you would have to do a subquery for 
*each* field in this case.

Your best bet here is to create a stored procedure. It will perform much 
better because it will not need to use subselects, but you'll still need to 
specify each of the fields to be updated. It would look something like:

FOR select t2.id, <other desired fields from Table 2>
  from Table2 t2
  inner join Table1 t1 on t1.ID = t2.ID
  INTO <local variables>
do begin
  update table2 set
    <assign each field from local variables>
  where id = <local id variable>
end

There's a way to optimize this even more using the built-in DB$KEY that can 
be queried from each record, but the above should perform well.

-- 
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca)