I have a table containing column names from another table. I want to run an update statement to update some values from that table, based off of the other.
EX:
TableA
ID|Column1|Column2
1 | 1.3 | 2.3
2 | 0 | 7
3 | 2.5 | 12.1
TableB
ID|ColumnName|MaxValue
1 | Column1 | NULL
2 | Column2 | NULL
Something along the lines of this:
So in this case, I would want to update MaxValue in TableB to be the max value from TableA where ColumnName is a colum in TableA.
Is this possible?
You can do it with a cursor and some dynamic sql. This isn't the best thing to do but if you needed a quick and dirty solution here you go:
DECLARE @colName VARCHAR(50), @str VARCHAR(2000), @id int
DECLARE c CURSOR FOR
SELECT id, columnName
FROM tableB
OPEN c
FETCH NEXT FROM c INTO @id, @columnName
WHILE @@fetch_status = 0
BEGIN
SET @str = 'update tableB set MaxValue = ( select max(' + @colName + ') from
tableA ) where id = ' + CONVERT(VARCHAR, @id)
EXEC ( @str )
FETCH NEXT FROM c INTO @id, @columnName
END
CLOSE c
DEALLOCATE c
If you do not want to use dynamic SQL, you could always do something like this
Update TableB
Set MaxValue = MaxValues.MaxValue
From TableB
Join
(
Select MaxValue = Max(Column1)
,ColumnName = 'Column1'
From TableA
Union All
Select MaxValue = Max(Column2)
,ColumnName = 'Column2'
From TableA
-- Union All ... and so on for all columns
) MaxValues
On TableB.ColumnName = MaxValues.ColumnName
Remember, if the TableA DDL changes, you must update this DML.
