Solved

SQL Update Where Exists If Then Else

Posted on 2010-08-17
3
333 Views
Last Modified: 2012-05-10
I am trying to update contents of a field in a table based on the results of a subquery.  I have been able to get the statement to work for an individual row, but I cannot figure out the syntax to run this update against all the rows in the table.

Basically the query looks to see if there was activity in another table for a given year and month and if sees activity update the base table with that count, and if it finds no activity it should update the field with a zero (0)

******** Single part version THIS WORKS BUT I NEED IT TO RUN AGAINS ALL PARTS IN THE BASE TABLE*********

If(EXISTS
(
select UnitsSold
from
(SELECT  partno,warehouse,YEAR(EntryDate) AS 'WOYear', MONTH(EntryDate) AS 'WOMonth', SUM(qty) AS 'UnitsSold',
count(wono) as "Workorders"
FROM woparts
GROUP BY partno,warehouse,YEAR(EntryDate), MONTH(EntryDate)
) C
where C.partno = '0040596'
and C.warehouse = 'inactive'
and C.WOMonth = month(dateadd(m,-3,getdate()))
and C.WOYear = year(dateadd(m,-3,getdate()))
))
      BEGIN
        UPDATE ab_partsdemand
            set demand4=
                  (
                  select UnitsSold
                  from
                  (SELECT  partno,warehouse,YEAR(EntryDate) AS 'WOYear', MONTH(EntryDate) AS 'WOMonth', SUM(qty) AS 'UnitsSold',
                  count(wono) as "Workorders"
                  FROM woparts
                  GROUP BY partno,warehouse,YEAR(EntryDate), MONTH(EntryDate)
                  ) C
                  where C.partno = '0040596'
                  and C.warehouse = 'inactive'
                  and C.WOMonth = month(dateadd(m,-3,getdate()))
                  and C.WOYear = year(dateadd(m,-3,getdate()))
                  )
                  where partno = '0040596'
                  and warehouse = 'inactive'
      END
else
        UPDATE ab_partsdemand
            set demand4=0
            where partno = '0040596'
            and warehouse = 'inactive'
0
Comment
Question by:josephpmo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 33459061
this subquery:
(
                  select UnitsSold
                  from
                  (SELECT  partno,warehouse,YEAR(EntryDate) AS 'WOYear', MONTH(EntryDate) AS 'WOMonth', SUM(qty) AS 'UnitsSold',
                  count(wono) as "Workorders"
                  FROM woparts
                  GROUP BY partno,warehouse,YEAR(EntryDate), MONTH(EntryDate)
                  ) C
                  where C.partno = '0040596'
                  and C.warehouse = 'inactive'
                  and C.WOMonth = month(dateadd(m,-3,getdate()))
                  and C.WOYear = year(dateadd(m,-3,getdate()))
)

has to return a single value. So my question is: what to do if it returns 2 or more values ? I figure the result you want in that case is the SUM, so put this:

       UPDATE ab_partsdemand
            set demand4=
                  (
                  select SUM(UnitsSold ) -- <<<<<<<<<<<<<<<<  This is the only line I changed
                  from
                  (SELECT  partno,warehouse,YEAR(EntryDate) AS 'WOYear', MONTH(EntryDate) AS 'WOMonth', SUM(qty) AS 'UnitsSold',
                  count(wono) as "Workorders"
                  FROM woparts
                  GROUP BY partno,warehouse,YEAR(EntryDate), MONTH(EntryDate)
                  ) C
                  where C.partno = '0040596'
                  and C.warehouse = 'inactive'
                  and C.WOMonth = month(dateadd(m,-3,getdate()))
                  and C.WOYear = year(dateadd(m,-3,getdate()))
                  )
                  where partno = '0040596'
                  and warehouse = 'inactive'

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33459106
You should optimize the YEAR(EntryDate) and Month(Entry) expressions to use the index by working out the date range, but if the partno+warehouse is selective enough, it is probably not an issue.
        UPDATE ab_partsdemand
            set demand4=
                  ISNULL((
                  (SELECT  SUM(qty)
                  FROM woparts
                  WHERE woparts.partno = ab_partsdemand.partno
                  AND woparts.warehouse = 'inactive'
                  AND YEAR(EntryDate) = year(dateadd(m,-3,getdate()))
                  AND MONTH(EntryDate) = month(dateadd(m,-3,getdate()))
                  ),0)
                  where warehouse = 'inactive'

Open in new window

0
 

Author Closing Comment

by:josephpmo
ID: 33459321
CyberKiwi,

Thanks a very elegant solution.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question