MS SQL

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

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jul 2009

Posts: 1

Reputation: fatjoez is an unknown quantity at this point

Solved Threads: 0

fatjoez

Offline

Newbie Poster

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

Jul 3rd, 2009

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

Help with Code Tags

(Toggle Plain Text)

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

•

Join Date: Feb 2009

Posts: 3,459

Reputation: sknake has much to be proud of

Solved Threads: 629

sknake

Online

.NET Enthusiast

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

Jul 3rd, 2009

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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

Last edited by sknake; Jul 3rd, 2009 at 1:19 pm.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Nov 2007

Posts: 5

Reputation: rathnakar is an unknown quantity at this point

Solved Threads: 0

rathnakar

Offline

Newbie Poster

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

Jul 14th, 2009

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

•

Originally Posted by 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
Help with Code Tags

(Toggle Plain Text)
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+"' ");
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....