Table Variable
-
Can a table variable be updated with the contents of another table variable?
Code:
----------
UPDATE
@tbl_Position
SET
prev_yield = [@tbl_Position_Previous_Day].[prev_yield]
WHERE
[@tbl_Position_Previous_Day].[sec_id]
= [@tbl_Position].[sec_id]
AND
[@tbl_Position_Previous_Day].[tick_lot]
= [@tbl_Position].[tick_lot]
This code checks ok, but will not run
Thanks
Andrew SQLDBA
-
You're missing the FROM clause ...
-
I cannot believe that, I have been sitting here way too long. Thanks for seeing that.
But hile I am here, Can I treat a table variable like a hard table? Can I update the contents of one table variable with another table variable.
Thanks
Andrew SQLDBA
-
Yes you can.
-
Here's an exemple of one of my procs :
UPDATE F SET NewRank = dtMatchedRanks.AvailableRanking
FROM @ForcedMoves F INNER JOIN
(SELECT CurrentRank, AvailableRanking
FROM
(SELECT TOP 100 PERCENT Count(*) AS Temp_id, F1.CurrentRank
FROM @ForcedMoves F1 INNER JOIN @ForcedMoves F2 ON F2.CurrentRank <= F1.CurrentRank
GROUP BY F1.CurrentRank
ORDER BY F1.CurrentRank
) dtCurrent
INNER JOIN
(SELECT TOP 100 PERCENT Count(*) AS Temp_id, A1.AvailableRanking
FROM @AvailableRanks A1 INNER JOIN @AvailableRanks A2 ON A2.AvailableRanking <= A1.AvailableRanking
GROUP BY A1.AvailableRanking
ORDER BY A1.AvailableRanking
) dtNew
ON dtCurrent.Temp_id = dtNew.Temp_id
) dtMatchedRanks
ON F.CurrentRank = dtMatchedRanks.CurrentRank
-
I have two different table variables, I am wanting to update one column in one table variable with the contents of the data in the second table variable.
I can select * from each table variable, I can see all the records in each. But when I try to update the first one, I keep getting errors. It will not allow me to alias each table variable. Here is the code that I have:
UPDATE
@tbl_Position
SET
prev_yield = prev.prev_yield
FROM
@tbl_Position_Previous_Day prev,
@tbl_Position pos
WHERE
prev.sec_id
= pos.sec_id
AND
prev.tick_lot
= pos.tick_lot
Thanks for any clue that you may give me on this.
Andrew SQLDBA
-
UPDATE
pos
SET
pos.prev_yield = prev.prev_yield
FROM
@tbl_Position_Previous_Day prev inner join
@tbl_Position pos
on
prev.sec_id
= pos.sec_id
AND
prev.tick_lot
= pos.tick_lot
-
Thank You
That worked great. I now have another table to join in this one. I will work with it, to try and figure it out.
Thank You again
Have a good one
Andrew
-
It just becomes a 3 tables joins... you should be able to figure it out
. -
Can you give me an example of the code that would create a 3 table join. The code that you sent me earlier worked great. I need to add one more table into that. I am going to keep trying, but it would be nice to have a guide to go by.
Thanks
Andrew
-
UPDATE
pos
SET
pos.prev_yield = prev.prev_yield
FROM
@tbl_Position_Previous_Day prev inner join
@tbl_Position pos
on
prev.sec_id
= pos.sec_id
AND
prev.tick_lot
= pos.tick_lot
inner join @Table3 T3 ON T3.id = pos.id
-
Thanks, I really appreciate it.
Have a good one
Andrew
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply