Please help with UPDATE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Please help with UPDATE

Author

Topic

siumui
Yak Posting Veteran

54 Posts

Posted - 2014-06-17 : 09:58:29

Hello all,

I'm having a problem with updating table and I can't figure out why.

Below are my codes:

DECLARE @ColName @VARCHAR(25) = NULL
SET @ColName =
(SELECT COLUMN_NAME
FROM information_schema.columns
WHERE TABLE_NAME = 'MyTable'
AND ORDINAL_POSITION = (SELECT MAX(Ordinal_Position)
FROM information_schema.columns
WHERE TABLE_NAME = 'MyTable'))

DECLARE @x int = 0
DECLARE @ColValues int
SET @ColValues =
(SELECT COUNT(Quantity) TotalQuantity
FROM OrderTable
WHERE YEAR(OrderDate) = YEAR(GETDATE())
AND MONTH(OrderDate) = MONTH(GETDATE()) - @x)

UPDATE MyTable
SET @ColName = @ColValues
WHERE Num = 1

The codes did not update the value for the specific column. Please help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-17 : 10:54:41

If you want to update columns dynamically (not sure why anyone would want to do that) you need to use branching execution (IF..ELSE) or you need dynamic sql.

Here is a link about dynamic sql, which is probably what you want to do:
http://www.sommarskog.se/dynamic_sql.html

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-17 : 10:55:06

Your final update would need to be dynamic sql for this to work

siumui
Yak Posting Veteran

54 Posts

Posted - 2014-06-17 : 11:14:40

Hello Lamprey, gbritton

That is just part of my codes only. I left out some variables which are counters for me to use in while loop later. I wanted to be able to see if it can update the table first before I really put in everything including all variables for counters increment to get the data for different month/year data and etc and be able to update it in the table for each specific column too.

This is the first time I heard of dynamic sql. I'm very new to sql. I'll view the link you provided and I hope I can understand what I need to do to make it work.

If you can please help elaborate a little bit more how to code or what I should use to make the codes work.

Thank you Lamprey and gbritton.

siumui
Yak Posting Veteran

54 Posts

Posted - 2014-06-17 : 11:48:56

Thank you all.

I have viewed the link provided by Lamprey and I got the codes to work. Thank you so much!!!!!

Again, thank you all!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources