I have 3 tables, below are their structures
( a bit of pseudo code )
I'm trying to update one table, with a variable column name, retrieved from another table
example row ( '1' , '', '' )
example row ( '1' , '23' )
example row ( '23', 'field1', 'abc' )
set order.field1 = abc
(retrieve column name "field1" from the "fieldname" field in fields table, and value from value in same table, where there is a match from order.id to produkt.id, produkt.produktnr to fields.produktnr
There are about 3000 rows in "order" table which need to be updated like this so I need to write a query that'll do the job...
I've searched and read a lot and am finding it quite difficult but I've gathered the following
It requires a mssql loop
and something like the following
DECLARE @colname varchar(20), @colvalue varchar(20),@colid int SELECT @colname = fieldname,@colvalue = value from fields f,produkt p,order o where o.id=p.order_id and p.produktnr = f.produktnr and o.id = @ID (not sure how but need the order id here) exec("update order set "+@colname+" = '"+@colvalue+"' where id = '"+@id+"' ");
I've gotten pretty far but I just don't know how to to...
get the @id in the select/update statement
make the code automatically get all the order id's and increment through them, setting @id for every increment
I would sincerely appreciate any help....