Query comparisons

[sds814]

I'm trying to convert the query below to use table variables, but it isn't giving the same results:

UPDATE #TEMP1
SET #TEMP1.ExternalTradeID =
(
Select #TEMP2.ExternalTradeID From #TEMP2 WHERE #TEMP2.TOLNK = #TEMP1.TOLNK
)
WHERE #TEMP1.TOLNK <> ''
AND #TEMP1.ExternalTradeID = ''

Conversion query:
UPDATE @FIST_Transactions
SET ExternalTradeID = ET.ExternalTradeID
FROM @FIST_Transactions FT
INNER JOIN (
SELECT ETID.ExternalTradeID
,ETID.TOLNK
FROM @FIST_Transactions FT
INNER JOIN @ExternalTradeID ETID
ON FT.TOLNK = ETID.TOLNK) ET
ON FT.TOLNK = ET.TOLNK
WHERE FT.TOLNK <> ''
AND FT.ExternalTradeID = ''

What am I doing wrong?

 

[bborissov]

Your original UPDATE statement could be changed to:

UPDATE #TEMP1 SET #TEMP1.ExternalTradeID = #TEMP2.ExternalTradeID
       FROM #TEMP1
INNER JOIN #TEMP2 ON #TEMP2.TOLNK = #TEMP1.TOLNK
WHERE #TEMP1.TOLNK <> '' 
  AND #TEMP1.ExternalTradeID = ''

Other query could be:

UPDATE Ft SET ExternalTradeID = ET.ExternalTradeID
      FROM @FIST_Transactions FT
INNER JOIN @ExternalTradeID ET ON FT.TOLNK = ET.TOLNK
WHERE FT.TOLNK <> ''
  AND FT.ExternalTradeID = ''

Borislav Borissov
VFP9 SP2, SQL Server

 

[sds814]

Thanks.

For some reason when I try the query below I don't get the same results with the query w/ temp tables.

UPDATE Ft SET ExternalTradeID = ET.ExternalTradeID
      FROM @FIST_Transactions FT
INNER JOIN @ExternalTradeID ET ON FT.TOLNK = ET.TOLNK
WHERE FT.TOLNK <> ''
  AND FT.ExternalTradeID = ''

Here is sample data:
TOLNK ExternalTradeID
20001000 500.00
20001000
20001000
20001000 500.01
20001000
20001000
20001000
20001000 500.02

I need to do the following:
1) Find the max externaltradeid for the tolnk. In this case it'll be 500.02.
2) Populate the rest of the ExternalTradeID as 500.02 for that ExternalTradeID.

So the data should be like:
TOLNK ExternalTradeID
20001000 500.02
20001000 500.02
20001000 500.02
20001000 500.02
20001000 500.02
20001000 500.02
20001000 500.02
20001000 500.02

So to get the max externaltradeid I have:

select tolnk, max(externaltradeid) as externaltradeid from @Transactions group by tolnk

I'm not sure how to do part 2 though. Thanks for the help.

 

[sds814]

I think the problem that I'm coming across is how to update a table that has a many to one relationship with another table. In this case, @Transactions is the table with many TOLNK. And the table variable with the TOLNK and max externaltradeid is the table with the one relationship. Is there a best or common practice for this scenario?