August 1, 2008 at 5:12 am
I’m try to update data from Item table and to do that I use a function that check if the model is equal to 1 or 0 and a model can be 1 or 0 .if I use top 1 it only use the value of the first record and update also the record that don’t match with record 1 .how can I do this so that it update in a correct way if value is 0 must update date to today date and if is 1 it must update date to null
create procedure spModel_U
as
begin
Declare @ItemID int,
@BandDate DateTime
set @ItemID = (select top 1 ItemID from Item)
set BandDate = (select top 1 BandDate
from Item
where BandDate is null)
if (@BandDate is null) and (dbo.fnConst_GetModel(@ItemID)= 0)
begin
update Item
set BandDate = getdate()
where dbo.fnConst_GetModel(@ItemID) = 0
end
if (dbo.fnConst_GetModel(@ItemID)= 1)
begin
update Item
set BandDate = null
where dbo.fnConst_GetModel(@ItemID) = 1
end
end
August 1, 2008 at 5:25 am
HI There,
Will this not help you?
UPDATE Item
SET BandDate = CASE WHEN dbo.fnConst_GetModel(ItemId) = 1 THEN NULL ELSE getdate() END
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 1, 2008 at 5:30 am
As far as I underrstood ur problem, you are already doing the job.
I dont know why are you going for TOP 1/
Just run the two queries as;
-- To Set BandDate = getdate() according to the conditions
update Item
set BandDate = getdate()
where dbo.fnConst_GetModel(@ItemID) = 0
-- To Set BandDate = NULL according to the conditions
update Item
set BandDate = null
where dbo.fnConst_GetModel(@ItemID) = 1
You can also use case to do this...
I think this will do the job.
Atif Sheikh
August 1, 2008 at 5:31 am
Atif Sheikh (8/1/2008)
As far as I underrstood ur problem, you are already doing the job.I dont know why are you going for TOP 1/
Just run the two queries as;
-- To Set BandDate = getdate() according to the conditions
update Item
set BandDate = getdate()
where dbo.fnConst_GetModel(@ItemID) = 0
AND BandDate is Null
-- To Set BandDate = NULL according to the conditions
update Item
set BandDate = null
where dbo.fnConst_GetModel(@ItemID) = 1
AND BandDate is Not Null
I think this will do the job.
Atif Sheikh
Careful, Atif, you're passing a variable to that function. You're updating nearly all BandDate depending on what the function returns from just one ItemID...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 5:31 am
Hi
but it does not check all the the rows to see if its 1 or 0 if take very row as if it have only 0 coz i'm using top 1 and if i dont use it it give me the subquery error
Thanx
August 1, 2008 at 5:35 am
Is that case statment solution not working???
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 1, 2008 at 5:46 am
Hi
if i remove Top 1 it give me an error Subquery returned more than 1 value.
set @ItemID = (select top 1 ItemID from Item)
set @BandDate = (select top 1 BandDate
from Item
where BandDate is null)
Thanx
August 1, 2008 at 5:52 am
LeeLuv
Please could you let me know the error you get with the Case statement solution above????
AS mentioned by the other helpers the subquery will prob not give you the correct results!
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 1, 2008 at 6:15 am
Hi
i'm sorry about that it work
Thanx a lot:)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply