Board index » delphi » Doing an update using an aggregate sub-query

Doing an update using an aggregate sub-query

I am not sure of the syntax to do an aggregate query in a sub-query.  In
pseudo code I want to do the following:

UPDATE TableA SET MaxDate =
SELECT Max(TableB.SentDate) As MaxDate
WHERE TableA.Ref=TableB.Ref

Since it is an aggregate query, I believe I need a GROUP BY in the query....

Todd

 

Re:Doing an update using an aggregate sub-query


Quote
Todd Cary <t...@aristesoftware.com> wrote:
> UPDATE TableA SET MaxDate =
> SELECT Max(TableB.SentDate) As MaxDate
> WHERE TableA.Ref=TableB.Ref

In MSSQL or Oracle you can do something like:

        UPDATE TableA
        SET MaxDate=(Select Max(TableB.SendDate) from TableB
                Where TableA.Ref=TableB.ReF)

which might sometimes result in a NULL value where there are no entries,
and will probably also result in a full table scan of both tables.

Oliver Townshend

Re:Doing an update using an aggregate sub-query


Quote
<oli...@zipworld.com.au> wrote:
> In MSSQL or Oracle you can do something like:

>    UPDATE TableA
>    SET MaxDate=(Select Max(TableB.SendDate) from TableB
>            Where TableA.Ref=TableB.ReF)

        This is standard SQL, so most DB servers should support it.

        -Craig

--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Re:Doing an update using an aggregate sub-query


Thanks!!

Todd

Quote
Craig Stuntz [TeamB] wrote:
><oli...@zipworld.com.au> wrote:

>>In MSSQL or Oracle you can do something like:

>>        UPDATE TableA
>>        SET MaxDate=(Select Max(TableB.SendDate) from TableB
>>                Where TableA.Ref=TableB.ReF)

>    This is standard SQL, so most DB servers should support it.

>    -Craig

Other Threads