difficult SQL statement that Im trying but no luck.

[Dupont666]

UPDATE TableA
SET TradeDate = min(b.TradeDate)
FROM TABLEA a, TABLEB b
WHERE a.DealId = b.DealId
and a.TransactionId = b.TransactionId
and b.Sendfile = 1
and min(b.TradeDate) < a.TradeDate

Comes back with the aggregate error, but Im not wanting to use a temp table as it needs to be done as the code goes along before moving to the next deal.

Any bright ideas?

[Size Nine Elm]

Try 'Select min(b.TradeDate)' instead fo just min...

[ewenm]

UPDATE tableA a
SET a.tradeDate = (Select min(b.tradeDate)
from tableb b
where a.dealID = b.dealID
and a.transactionID = b.transactionID
and b.sendfile = 1
and b.tradeDate < a.tradeDate)

Try that. You might need to put another where clause at the end to specify the tableA record you want to update.

Edited by ewenm on Friday 16th April 18:00

[otolith]

Not clear what you are trying to do - is there meant to be some aggregation or not?

Are you trying to set TradeDate in TableA to the minimum value of TradeDate in all of TableB or to the minimum value of TradeDate in TableB with the same deal and/or transaction ID?

If it's the latter, a subquery containing the aggregate is how I would do it - something like

UPDATE 
    TableA
SET
    TradeDate=X.TradeDate
FROM
    TableA A 
    join
    (
        SELECT 
            TransactionID,
            DealID,
            min(TradeDate) as TradeDate
        FROM
            TableB
        GROUP BY
            TransactionID,
            DealID
    ) X on X.TransactionID=A.TransactionID and X.DealID=A.DealID
WHERE
    X.TradeDate < A.TradeDate

or something like it.

[Dupont666]

otolith said:

Not clear what you are trying to do - is there meant to be some aggregation or not?

Are you trying to set TradeDate in TableA to the minimum value of TradeDate in all of TableB or to the minimum value of TradeDate in TableB with the same deal and/or transaction ID?

If it's the latter, a subquery containing the aggregate is how I would do it - something like

UPDATE 
    TableA
SET
    TradeDate=X.TradeDate
FROM
    TableA A 
    join
    (
        SELECT 
            TransactionID,
            DealID,
            min(TradeDate) as TradeDate
        FROM
            TableB
        GROUP BY
            TransactionID,
            DealID
    ) X on X.TransactionID=A.TransactionID and X.DealID=A.DealID
WHERE
    X.TradeDate < A.TradeDate

or something like it.

you got the winner, effectively I put a temp table in the middle of the query in the form of a derived table.... it was a bugger to work out.

[ewenm]

You might find that inefficient as you group all the records in table B each time you call it, rather than just looking at the records you're interested in.

[Dupont666]

ewenm said:

You might find that inefficient as you group all the records in table B each time you call it, rather than just looking at the records you're interested in.

in my case its one record in the derived table at a time