Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Update table from select statement

I have the following select statement.  

SELECT partnum, max(tranDate) lastTranDate, min(tranDate) DATEADD,count(*) FROM parttran WHERE trantype in ('DMR-MTL', 'INS-MTL', 'PLT-MTL', 'PUR-MTL','STK-MTL','MFG-CUS','MFG-PLT','MFG-STK','PLT-ASM','PUR-INS','PUR-STK','PUR-UKN','STK-ASM') group by partnum having max(tranDate) < dateadd(year, -2, getdate())

Based on the "PartNum" that are presented, I need to do an update statement like so

Update Part Set CheckBox01 = '1'

So my quesiton, how can I update a table from the result of this select statement.

0
chrisryhal
Asked:
chrisryhal
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
JestersGrindCommented:
You could probably do it with a CTE like this.

Greg


;WITH AggregatedData
AS
(
SELECT partnum, max(tranDate) lastTranDate, min(tranDate) DATEADD,count(*) 
FROM parttran 
WHERE trantype in ('DMR-MTL', 'INS-MTL', 'PLT-MTL', 'PUR-MTL','STK-MTL','MFG-CUS','MFG-PLT','MFG-STK','PLT-ASM','PUR-INS','PUR-STK','PUR-UKN','STK-ASM') 
GROUP BY partnum HAVING MAX(tranDate) < dateadd(year, -2, getdate())
)
UPDATE Part SET CheckBox01 = '1'
WHERE partnum IN(SELECT partnum FROM AggregatedData)

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
One method is to use an UPDATE with JOIN.
UPDATE p 
SET p.CheckBox01 = '1'
FROM Part p
JOIN (
   SELECT partnum
   FROM parttran
   WHERE trantype IN (
      'DMR-MTL', 'INS-MTL', 'PLT-MTL', 'PUR-MTL', 
      'STK-MTL', 'MFG-CUS', 'MFG-PLT', 'MFG-STK', 
      'PLT-ASM', 'PUR-INS', 'PUR-STK', 'PUR-UKN', 
      'STK-ASM'
   ) 
   GROUP BY partnum 
   HAVING MAX(tranDate) < DATEADD(year, -2, GETDATE())
) t ON t.partnum = p.partnum
;

Open in new window


You can probably simplify this using an IN or EXISTS clause. I would probably go with the latter. But try the above as it is probably easiest to transition to from the code you already have.
0
 
Kevin CrossChief Technology OfficerCommented:
Sorry, Greg. I was typing and did not see your comment. That is a good example of the IN syntax. Using CTE helps to transition from existing code easily also.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
HainKurtSr. System AnalystCommented:
maybe this:

Update Part Set CheckBox01 = '1'
where some_column in (your select statement, but just the required id in select part...)
0
 
JestersGrindCommented:
No problem, Kevin.  Actually, it doesn't say what version of SQL it is.  Mine will only work on 2005 and higher.  Yours will work on SQL 2000 as well.

Greg

0
 
chrisryhalAuthor Commented:
Its 2005
0
 
chrisryhalAuthor Commented:
Both soutions worked.  Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now