July 16, 2007 at 7:14 am
I'm hoping someone will have had experience doing this before.
I want to build a dynamic query that will loop through a list of keys from another table. Here's what I have so far:
update tbldlmatrix
Set col = (SELECT MIN(Col) AS Expr1
FROM tblDLMatrix
WHERE (Dkey = dynamic varaible)
GROUP BY Dkey
where dkey = dynamic varaible and
col > (SELECT MIN(Col) AS Expr1
FROM tblDLMatrix
WHERE (Dkey = dynamic varaible)
GROUP BY Dkey)
The dynamic variable would be the result of this query:
Select varname from tblDKeys
Can someone give me some pointers on how I can accomplish this? Can I use a table variable to hold the varname? If so, How do I reference it in teh update query?
Thanks for your help!
Darryl
July 16, 2007 at 7:50 am
Will this solve your problem ?
update tbldlmatrix
Set col = (SELECT MIN(Col) AS Expr1
FROM tblDLMatrix
WHERE (Dkey IN (Select varname from tblDKeys))
GROUP BY Dkey
where dkey IN (Select varname from tblDKeys) and
col > (SELECT MIN(Col) AS Expr1
FROM tblDLMatrix
WHERE (Dkey IN (Select varname from tblDKeys))
GROUP BY Dkey)
July 16, 2007 at 8:20 am
yes you can use table variables
DECLARE
@totalCount INT
DECLARE @tracker INT
DECLARE @Dkey AS datatype
SET
@tracker = 0
DECLARE
@tblVariable TABLE
(
tId INT IDENTITY(1,1),
Dkey int
)
INSERT
INTO @tblVariable
SELECT DISTINCT varname
FROM tblDKeys
SELECT
@totalCount = COUNT(tId)
FROM @tblVariable
WHILE
@tracker < @totalCount + 1
BEGIN
SELECT DISTINCT TOP 1 @Dkey = Dkey
FROM @tblVariable
WHERE tId = @tracker + 1
UPDATE tblDLMatrix
SET col = MinCol
FROM tblDLMatrix A,
(
SELECT MIN(Col) AS MinCol
FROM tblDLMatrix
WHERE (Dkey = @Dkey)
GROUP BY Dkey
) B
WHERE Dkey = @Dkey AND A.col > B.MinCol
SET @tracker = @tracker + 1
END
-
or you can consider using cursors
July 20, 2007 at 6:28 am
Thanks bledu I appreciate your assistance!
It works perfectly!
Cheers!
Darryl
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply