?
Solved

Update table from select statement

Posted on 2011-09-22
7
Medium Priority
?
383 Views
Last Modified: 2012-05-12
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
Comment
Question by:chrisryhal
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 1000 total points
ID: 36582248
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
 
LVL 61

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1000 total points
ID: 36582294
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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36582302
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:HainKurt
ID: 36582304
maybe this:

Update Part Set CheckBox01 = '1'
where some_column in (your select statement, but just the required id in select part...)
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36582319
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
 
LVL 2

Author Comment

by:chrisryhal
ID: 36582368
Its 2005
0
 
LVL 2

Author Closing Comment

by:chrisryhal
ID: 36582577
Both soutions worked.  Thanks again
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

621 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