DECLARING A VARIABLE - 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

General SQL Server Forums

New to SQL Server Programming

DECLARING A VARIABLE

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-03-11 : 19:28:47

Could any body please help me in writing a stored procedure for updating the data ,where finish_day is > than the finish_day in temp table ?

i guess i need to declare a variable , finish_day is datetime fieild,

Create PROCEDURE [dbo].[Proc_DiffData_HOSTDATA_AGG]
AS
BEGIN

SET NOCOUNT ON;

Select * from dbo.DW_T_ASW_HOSTDATA_AGG_TEMP where FINISH_DAY >
(select FINISH_DAY from dbo.DW_T_ASW_HOSTDATA_AGG )

END

i get an error if i use above syntax...

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

how do i get the data from dbo.DW_T_ASW_HOSTDATA_AGG_Temp table where finish_day is greater than the finish_day in dbo.DW_T_ASW_HOSTDATA_AGG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 19:53:18

your sub query " select FINISH_DAY from dbo.DW_T_ASW_HOSTDATA_AGG " return more than 1 record. Which one do you want to compare with ?

KH
[spoiler]Time is always against us[/spoiler]

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-12 : 13:27:32

Run this to see if it works for you.

declare @your_table table(id int, d datetime, test varchar(50))
insert into @your_table values(1, '1/2/2010', 'original value')
insert into @your_table values(16, '1/1/2010', 'original value')
insert into @your_table values(13, '2/1/2010', 'original value')
insert into @your_table values(21, '3/21/2010', 'original value')
insert into @your_table values(11, '2/22/2010', 'original value')
insert into @your_table values(10, '2/2/2010', 'original value')
select * from @your_table order by d

declare @temp_finish table(temp_id int, finish_day datetime)
insert into @temp_finish values(17, '4/2/2010')
insert into @temp_finish values(16, '1/12/2010')
insert into @temp_finish values(143, '2/2/2010')
insert into @temp_finish values(1, '3/21/2010')
insert into @temp_finish values(11, '5/22/2010')
insert into @temp_finish values(15, '1/2/2010')
select * from @temp_finish order by finish_day

-- select row to update -------
select
distinct(t1.id) as 'id of row needs update'
from @your_table t1
cross apply
(select * from @temp_finish t2
where t1.d > t2.finish_day) as A
order by t1.id

-- update table ----
update @your_table set test = 'update ...'
where id in
(
select
distinct(t1.id)
from @your_table t1
cross apply
(select * from @temp_finish t2
where t1.d > t2.finish_day) as A
)

-- check update result -------
select * from @your_table order by id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources