Re: Update

Article View: borland.public.interbase.sql

Article #259

Back to borland.public.interbase.sql Previous Next

#259

From: Wayne Niddery (TeamB)
Date: Fri, 04 Jan 2008 16:56

32 lines
2161 bytes

"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)

Technical Details

Message-ID: <477e655d$1@newsgroups.borland.com>

Path:

arc1-ca.pugleaf.net!g2news1.google.com!news4.google.com!feeder1-2.proxad.net!proxad.net!feeder1-1.proxad.net!feeder.news-service.com!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

References: <477dee51$1@newsgroups.borland.com>

Show Full Headers

Complete Article Headers

X-Google-Thread: 114a0e,1871257dc9ffee7f
X-Google-Attributes: gid114a0e,public,usenet
X-Google-Language: ENGLISH,ASCII-7-bit
From: "Wayne Niddery \(TeamB\)" <wniddery@chaffaci.on.ca>
Newsgroups: borland.public.interbase.sql
References: <477dee51$1@newsgroups.borland.com>
In-Reply-To: <477dee51$1@newsgroups.borland.com>
Subject: Re: Update
Date: Fri, 4 Jan 2008 11:56:58 -0500
Lines: 33
MIME-Version: 1.0
Content-Type: text/plain;
 format=flowed;
 charset="iso-8859-1";
 reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6000.16480
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16545
NNTP-Posting-Host: 24.137.214.213
Message-ID: <477e655d$1@newsgroups.borland.com>
X-Trace: newsgroups.borland.com 1199465821 24.137.214.213 (4 Jan 2008 08:57:01 -0700)
X-Authenticated-User: wniddery
Path: arc1-ca.pugleaf.net!g2news1.google.com!news4.google.com!feeder1-2.proxad.net!proxad.net!feeder1-1.proxad.net!feeder.news-service.com!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

Reply to Parent Message