Update from temp table - 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 2005 Forums

Transact-SQL (2005)

Update from temp table

Author

Topic

BrettD
Starting Member

14 Posts

Posted - 2008-06-09 : 09:18:42

I'm trying to update a table from a temporary table but I'm struggling a little with the syntax. Here's what I have so far:

DECLARE @WeekNo INT
SET @WeekNo = 1

WHILE @WeekNo <= 53
BEGIN 
SET @SQL = 'UPDATE [Targets] 
    SET [Target] =  SELECT ISNULL(Week' + Cast(@WeekNo as VarChar(2)) + ', 0) FROM #targets
    WHERE [CampaignID] = @CampaignID'
    SET @WeekNo = @WeekNo + 1
EXECUTE (@SQL)
END

My temp table gets created OK, it's just setting the 'Target' field to the value of the same field in my temp table.

I get the following errors:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ' + Cast(@WeekNo as VarChar(2)) + '.

Can anyone help?

Thanks in advance,
Brett

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-09 : 09:24:25

[code]DECLARE @WeekNo INT
SET @WeekNo = 1

WHILE @WeekNo <= 53
BEGIN
SET @SQL = '
UPDATE t
SET t.Target = (SELECT COALESCE(y.Week' + CAST(@WeekNo AS VARCHAR(2)) + ', 0) FROM #Targets AS y)
FROM Targets AS t
WHERE t.CampaignID = ' + CAST(@CampaignID AS VARCHAR(12))

SET @WeekNo = @WeekNo + 1

EXECUTE (@SQL)
END[/code]

E 12°55'05.25"
N 56°04'39.16"

BrettD
Starting Member

14 Posts

Posted - 2008-06-09 : 09:49:39

Peso, thanks for the swift reply. I've added another condition in my where clause so only the correct week gets updated.

DECLARE @WeekNo INT
SET @WeekNo = 1

WHILE @WeekNo <= 53
BEGIN
SET @SQL = '
UPDATE t
SET t.Target = (SELECT COALESCE(y.Week' + CAST(@WeekNo AS VARCHAR(2)) + ', 0) FROM #targets AS y)
FROM Targets AS t
WHERE t.CampaignID = ' + CAST(@CampaignID AS VARCHAR(12)) + '
AND t.WeekNumber = ' + CAST(@WeekNo as VARCHAR(12))

SET @WeekNo = @WeekNo + 1
EXECUTE (@SQL)
END

-- Drop temporary tables
DROP TABLE #targets;

However, I now get an error:
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Obviously I should only get on value returned from my temp table. Any ideas why it's returning more than one?

Brett

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-09 : 09:52:51

Don't forget to also put the filter in the correlated subquery.

E 12°55'05.25"
N 56°04'39.16"

BrettD
Starting Member

14 Posts

Posted - 2008-06-09 : 09:59:54

SET t.Target = (SELECT COALESCE(y.Week' + CAST(@WeekNo AS VARCHAR(2)) + ', 0) FROM #targets AS y where t.WeekNumber = ' + CAST(@WeekNo as VARCHAR(12))
)

Like that? I've never done this sort of thing in SQL before, I'm a self-confessed SQL idiot!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-09 : 10:02:50

Post your table definitions here, please.

Read http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
and http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

BrettD
Starting Member

14 Posts

Posted - 2008-06-09 : 10:13:18

Temp table:

CREATE TABLE [dbo].[#targets](
	[ID] [int] NOT NULL,
	[Week1] [int] NULL,
	[Week2] [int] NULL,
	[Week3] [int] NULL,
	[Week4] [int] NULL,
	[Week5] [int] NULL,
	[Week6] [int] NULL,
	[Week7] [int] NULL,
	[Week8] [int] NULL,
	[Week9] [int] NULL,
	[Week10] [int] NULL,
	[Week11] [int] NULL,
	[Week12] [int] NULL,
	[Week13] [int] NULL,
	[Week14] [int] NULL,
	[Week15] [int] NULL,
	[Week16] [int] NULL,
	[Week17] [int] NULL,
	[Week18] [int] NULL,
	[Week19] [int] NULL,
	[Week20] [int] NULL,
	[Week21] [int] NULL,
	[Week22] [int] NULL,
	[Week23] [int] NULL,
	[Week24] [int] NULL,
	[Week25] [int] NULL,
	[Week26] [int] NULL,
	[Week27] [int] NULL,
	[Week28] [int] NULL,
	[Week29] [int] NULL,
	[Week30] [int] NULL,
	[Week31] [int] NULL,
	[Week32] [int] NULL,
	[Week33] [int] NULL,
	[Week34] [int] NULL,
	[Week35] [int] NULL,
	[Week36] [int] NULL,
	[Week37] [int] NULL,
	[Week38] [int] NULL,
	[Week39] [int] NULL,
	[Week40] [int] NULL,
	[Week41] [int] NULL,
	[Week42] [int] NULL,
	[Week43] [int] NULL,
	[Week44] [int] NULL,
	[Week45] [int] NULL,
	[Week46] [int] NULL,
	[Week47] [int] NULL,
	[Week48] [int] NULL,
	[Week49] [int] NULL,
	[Week50] [int] NULL,
	[Week51] [int] NULL,
	[Week52] [int] NULL,
	[Week53] [int] NULL
	)

Targets table:

CREATE TABLE [dbo].[Targets](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CampaignID] [int] NOT NULL,
	[BranchID] [int] NOT NULL,
	[WeekNumber] [int] NOT NULL,
	[PeriodNumber] [int] NOT NULL,
	[YearNumber] [varchar](10) NOT NULL,
	[Target] [decimal](10, 2) NOT NULL,
 CONSTRAINT [PK_Targets] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Apologies for not sharing these upfront.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-09 : 10:21:11

No need for dynamic SQL at all, it seems...

UPDATE		t
SET		t.Target = d.theValue
FROM		Targets
INNER JOIN	(
			SELECT	CONVERT(INT, REPLACE(p.theWeek, 'Week', '')) AS theWeekNumber,
				p.theValue
			FROM	#Targets AS t
			UNPIVOT	(
					theValue
					FOR theWeek IN (Week1, Week2, Week3, Week4, Week5, Week6, Week7, Week8,
							Week9, Week10, Week11, Week12, Week13, Week14, Week15,
							Week16, Week17, Week18, Week19, Week20, Week21, Week22,
							Week23, Week24, Week25, Week26, Week27, Week28, Week29,
							Week30, Week31, Week32, Week33, Week34, Week35, Week36,
							Week37, Week38, Week39, Week40, Week41, Week42, Week43,
							Week44, Week45, Week46, Week47, Week48, Week49, Week50,
							Week51, Week52, Week53)
				) AS p
		) AS d ON d.theWeekNumber = t.WeekNumber
WHERE		t.WeekNumber = @WeekNumber

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources