August 9, 2004 at 8:08 am
I saw a post recently using CASE for updates. When I try to update multiple rows using this statement, the first rows matched work fine, but then subsequent rows are updated with the matched row value and not the proper value. Am I trying to make something work that is not supported? Below is the sample of the (dynamic) sql. Do I have to use a cursor for this? Any insights and guidance are appreciated.
declare @stockdate datetime, @MOMstockDate datetime, @EOMstockDate datetime
SET @stockdate = '07/13/2004'
SET @EOMstockdate =
cast(month(@stockdate) as varchar(2)) + '/' + "
cast(day (dateadd(dd,-(day(dateadd(mm,1,@stockdate ))),dateadd(mm,1,@stockdate )) ) AS varchar(2)) + '/' +
cast(year(@stockdate) as varchar(4) )
SET @MOMstockDate = cast(month(@stockdate)as varchar(2)) + '/15/' + cast(year(@stockdate) as varchar(4))
UPDATE " & ItemTable
SET ModifyDate = getdate(),
stockDate =
case "
when isNULL(b.itemsStockPeriod,'') = 3 then @stockdate
when isNULL(b.itemsStockPeriod,'') = 1 then @EOMstockdate
when isNULL(b.itemsStockPeriod,'') = 2 and day(@stockdate) > 15 then @EOMstockDate
when isNULL(b.itemsStockPeriod,'') = 2 and day(@stockdate) < 16 then @MOMstockDate
else @EOMstockDate
end
from items b
where itemNO = 'Abc123'
and " & ItemTable & ".CompanyID = '" & CompanyID & "'"
and " & ItemTable & ".MYear = '" & MYear & "'"
and b.MYear = " & ItemTable & ".MYear
and b.item = " & ItemTable & ".itemType
and b.Description = " & ItemTable & ".ItemDescription
and IsNull(" & ItemTable & ".stockDate,'1/1/80') <= '1/1/80'
and (SELECT count(*) FROM items (nolock)
WHERE item = " & ItemTable & ".itemType
AND CompanyID = '" & CompanyID & "'"
AND MYear = '" & MYear & "'"
AND itemType <> 'Recall') > 0
ItemTable
ItemNO, ItemType, stockDate, CompanyID, Myear
ABC123,A,NULL,ACME,2004
ABC123,B,NULL,ACME,2004
ABC123,C,NULL,ACME,2004
Items
Myear, Item, Description, ItemStockPeriod, CompanyID
2004,A,ballistic module,1,ACME
2004,B,rocket module,3,ACME
2004,C,jet module,1,ACME
2004,D,elastic module,1,ACME
2004,E,sedimentary module,2,ACME
Result
ItemTable
ItemNO, ItemType, stockDate, CompanyID, Myear
ABC123,A,'07/31/2004',ACME,2004
ABC123,B,'07/13/2004',ACME,2004
ABC123,C,'07/13/2004',ACME,2004 <== should be 07/31/2004
August 10, 2004 at 8:33 am
I used your example in the code bellow and it seems to work fine. Few suggestions:
- Write static SQL first to make it work (I had problems with ambigous column names so make sure to qualify all column references even if names are unique). This will also give you a good idea of what part of the statement is dynamic.
- Use ANSI style joins in UPDATE statement. This way you can change table name but join condition remains the same.
- See if you can avoid dynamic SQL
- Subquery to calculate count didn't seem to be necessary as itemType <> 'Recall' could be specified in the main join. Also all COUNT > 0 type subqueries could and should be replaced with EXISTS type subqueries.
drop table #ItemTable
create table #ItemTable(
ItemNO char(6)
, ItemType char(1)
, stockDate datetime
, CompanyID char(4)
, Myear int
)
insert #ItemTable( ItemNO , ItemType , stockDate , CompanyID , Myear)
select 'ABC123' , 'A' , NULL , 'ACME' , 2004
union all
select 'ABC123' , 'B' , NULL , 'ACME' , 2004
union all
select 'ABC123' , 'C' , NULL , 'ACME' , 2004
drop table #Items
create table #Items(
Myear int
, Item char(1)
, Description varchar(20)
, ItemsStockPeriod tinyint
, CompanyID char(4)
)
insert #Items( Myear , Item , Description , ItemsStockPeriod , CompanyID )
select 2004 , 'A' , 'ballistic module' , 1 , 'ACME'
union all
select 2004 , 'B' , 'rocket module' , 3 , 'ACME'
union all
select 2004 , 'C' , 'jet module' , 1 , 'ACME'
union all
select 2004 , 'D' , 'elastic module' , 1 , 'ACME'
union all
select 2004 , 'E' , 'sedimentary module' , 2 , 'ACME'
select * from #ItemTable
select * from #Items
declare
@stockdate datetime
, @MOMstockDate datetime
, @EOMstockDate datetime
SET @stockdate = '07/13/2004'
SET @EOMstockdate = cast(month(@stockdate) as varchar(2))
+ '/' + cast(day (dateadd(dd, -(day(dateadd(mm, 1, @stockdate))), dateadd(mm, 1, @stockdate))) AS varchar(2))
+ '/' + cast(year(@stockdate) as varchar(4) )
SET @MOMstockDate = cast(month(@stockdate)as varchar(2))
+ '/15/' + cast(year(@stockdate) as varchar(4))
UPDATE a -- ModifyDate column not specified in #ItemTable
SET stockDate = case
when isNULL(b.itemsStockPeriod,'') = 3
then @stockdate
when isNULL(b.itemsStockPeriod,'') = 1
then @EOMstockdate
when isNULL(b.itemsStockPeriod,'') = 2 and day(@stockdate) > 15
then @EOMstockDate
when isNULL(b.itemsStockPeriod,'') = 2 and day(@stockdate) < 16
then @MOMstockDate
else
@EOMstockDate
end
from #ItemTable a
join #Items b
on b.CompanyID = a.CompanyID
and b.MYear = a.MYear
and b.item = a.itemType
-- and b.Description = a.ItemDescription --< ItemDescription column not specified in #ItemTable
where a.itemNO = 'Abc123'
and IsNull(a.stockDate, '1/1/80') <= '1/1/80'
and a.itemType <> 'Recall'
select * from #ItemTable
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply