Update sql database table with UPDate Instruction using variable column name

Question

Post reply

Update sql database table with UPDate Instruction using variable column name

amuzelog

SSC Journeyman

Points: 86
More actions
March 2, 2011 at 9:07 pm

#235408

Hi Everyone
I have 2 tables which are Table_1 and Table_2.
Table_1 has the following structure
TestDate DateTime,
TestID SmallInt,
Q1 TinyInt,
Q2 TinyInt,
.
.
.
Q10 TinyInt
Table_2 has the following structure
TestDate DateTime,
TestId SmallInt,
Qn1 TinyInt, Default 0
Qn2 TinyInt, Default 0
.
.
.
Qn99 TinyInt Default 0
Table_1 has the records on each row one possible combination of 10 questions for each day and test id number from all combinations taken 10 from hundred questions with no repetition.
I want update Tbale_2 from Table_1 as the follow:
_ Insert Test Date, Test ID
_ Update these 10 columns with 1 whose questions were present in the combination for that Test Date and Test ID in Table_1
I was experimenting the following for testing the update with variable column name (is not the real world problem)
CREATE TABLE [dbo].[Table_2](
[IdNum] [smallint] NULL,
[Colum1] [nchar](10) NULL,
[Colum2] [nchar](10) NULL,
[Colum3] [nchar](10) NULL,
[Colum4] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT INTO [Test].[dbo].[Table_2]
VALUES (1,9,10,15,89)
GO
INSERT INTO [Test].[dbo].[Table_2]
VALUES (2,15,23,55,79)
GO
INSERT INTO [Test].[dbo].[Table_2]
VALUES (3,55,60,75,99)
GO
--
-- Testing the update
--
declare @column char(128)
declare @col_num int
Set @col_num=1 --- I choose Column 1 to be updated
set @column=QUOTENAME('Colum'+CAST(@Col_num as NCHAR(10)))
set @column=LTRIM(rtrim(@column))
print @column
update dbo.Table_1
set @column = '3'
from dbo.Table_1
where dbo.Table_1.IdNum=12
go
But it dose not update the column I choose
Has anyone a solution for this and for my real problem.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 1

Try putting the last update statement in an

EXEC (@MyUpdateSQLStatement);

...kind of thing.

If you receive an error, it is best to post it along with your question.

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

It would seem to me that if you are having to do this sort of dynamic update on a regular basis, something is wrong with you design.

If you can revisit that and correct and your 'problem' will go away

Clear Sky SQL
My Blog[/url]

amuzelog SSC Journeyman Points: 86 More actions · Answer 3

Thanks Jim and all of you for help

But if I put the last statement as dynamic update, that will work. That mean the initial problem I have will require 10 dynamic update to solve the problem. There any other options much faster then this? If could please let me know to deal with this issue. Thanks again.

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 4

Amuzelog,

Yes, the design can be greatly improved, however, it is difficult to determine exactly what is needed since the example tables are very generic with no apparent relation between them.

It would be better for you to post a more accurate table definition and what you are trying to accomplish.

Since this is a TSQL question, it is best posted in the 2005 TSQL forum, rather than the general 2005 forum. This helps keep the information clean and organized and to catch the attention of the proper people to help.

Jim

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph