Solved

Update table from select statement

Posted on 2011-09-22
7
377 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 250 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 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 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 59

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 51

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

776 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