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

[fatjoez]

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

[sknake]

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

[rathnakar]

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

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