SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 problem with update set subquery
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

reignheart05
Starting Member

3 Posts

Posted - 12/06/2012 :  05:23:24  Show Profile  Reply with Quote
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

Edited by - reignheart05 on 12/06/2012 05:28:19

reignheart05
Starting Member

3 Posts

Posted - 12/06/2012 :  05:55:53  Show Profile  Reply with Quote
i will be trying the IN clause.. hopefully it will work
Go to Top of Page

reignheart05
Starting Member

3 Posts

Posted - 12/07/2012 :  01:11:38  Show Profile  Reply with Quote
can anyone help me out here? :(
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 12/07/2012 :  02:18:25  Show Profile  Reply with Quote
quote:
Originally posted by reignheart05

can anyone help me out here? :(


Can you post exact error message once?

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000