Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update table from select statement

Posted on 2011-09-22
7
Medium Priority
?
381 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
[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
  • 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 60

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 60

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 59

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

715 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