Updating a table with variable column name, retrieved from another table ?
 

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

order
[id,field1,field2]
example row ( '1' , '', '' )

produkt
[order_id,produktnr]
example row ( '1' , '23' )

fields
[produktnr,fieldname,value]
example row ( '23', 'field1', 'abc' )

Example update

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....

Re: Updating a table with variable column name, retrieved from another table ?
 

You can use a cursor to iterate for every @id. Here is sample data and code to demonstrate the concept:

IF OBJECT_ID('OrdersTest', 'U') IS NOT NULL Drop Table OrdersTest

Create Table OrdersTest

(

  OrderId int identity(1000, 1) PRIMARY KEY NONCLUSTERED,

  CustName varchar(30)

)

GO

Insert Into OrdersTest (CustName) Values ('Scott')

Insert Into OrdersTest (CustName) Values ('Bob')

Insert Into OrdersTest (CustName) Values ('Dave')

Insert Into OrdersTest (CustName) Values ('Frank')

GO



--OK done setting up test data, start the code.

Declare @OrderId int



DECLARE det_cursor CURSOR FOR

  Select OrderId From OrdersTest (NOLOCK) Order By OrderId



OPEN det_cursor



FETCH NEXT FROM det_cursor INTO @OrderId



WHILE (@@FETCH_STATUS = 0)

BEGIN

  exec('print ' + @OrderId)

  FETCH NEXT FROM det_cursor INTO @OrderId

END



CLOSE det_cursor

DEALLOCATE det_cursor

Re: Updating a table with variable column name, retrieved from another table ?
 

hi friend,
followind queries will help u,

update table1 set column1=b.column1 from table1 a,table2 b
where a.column2=b.column2
or
update table1 set column1='ap' where dist in(select dist from tabl2)
regards,
rathnakar