problem with update set subquery

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)

problem with update set subquery

Author

Topic

reignheart05
Starting Member

3 Posts

Posted - 2012-12-06 : 05:23:24

DROP TABLE SalesData;
-- use database

-- create table
CREATE TABLE SalesData
(
--P_Id int,
[Store No_] varchar(255),
);

DECLARE @vFrom Datetime;
DECLARE @vTo Datetime;
DECLARE @vStr nvarchar(500);
DECLARE @Query nvarchar(500);

--set your dates from here
SET @vFrom = '20121127';
SET @vTo = '20121205';

WHILE @vFrom < @vTo
BEGIN
SET @vStr = Replace(convert(varchar,@vFrom,102),'.','')
PRINT @vStr;
SET @Query = 'ALTER TABLE dbo.SalesData ADD "' + @vStr + '" decimal(16,2) DEFAULT 0.00';
EXEC(@Query);
Set @vFrom = DATEADD(day,1,@vFrom)
END;

--insert of dates columns first
INSERT INTO SalesData ([Store No_]) (Select [No_] FROM [db].[dbo].[sTORE] Group By [No_]);

--set your dates here same as the top
SET @vFrom = '20121127';
SET @vTo = '20121205';
WHILE @vFrom < @vTo
BEGIN
SET @vStr = Replace(convert(varchar,@vFrom,102),'.','')
PRINT @vStr;
SET @QUERY = 'UPDATE SalesData
SET ['+@vStr+'] =
(Select SUM([Net Amount])
FROM [db].[dbo].[Transaction Header]
WHERE [Date] =
''' +@vstr+ '''
Group By [Store No_])
WHERE [Store No_] =
(Select [Store No_]
FROM [db].[dbo].[Transaction Header]
WHERE [Date] = '''+@vstr+''' Group By [Store No_])';
--print @query;
EXEC(@QUERY);

Set @vFrom = DATEADD(day,1,@vFrom);
END;

Select * from SalesData ORDER BY [Store No_];

in sqlserver 2008 this query works but at 2005 i encountered this problem.. and i dont know how to solve it. can anyone help me out? thanks

the error begins at set update field 1 = subquery
where = subquery

reignheart05
Starting Member

3 Posts

Posted - 2012-12-06 : 05:55:53

i will be trying the IN clause.. hopefully it will work