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

Expand Post »

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

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

Similar Threads

Collect sum of a column in a html table? in JavaScript / DHTML / AJAX
How to Get Column names of a table of MS Access (.mdb)- ODBC Connection in C#
sqlite3-- how to see column names for table in Python
How to read the data from Table Variable in MS SQL
Add Column to Access Table with Macro in MS Access and FileMaker Pro

fatjoez

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Jul 2009

Permalink

Jul 3rd, 2009

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:

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.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Jul 14th, 2009

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

Click to Expand / Collapse Quote 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
(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....

rathnakar

Reputation Points: 9

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Nov 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Update table row without firing trigger?

Next Thread in MS SQL Forum Timeline: Using IF ELSE in script

DaniWeb Message

Cancel Changes

User Name
Password