Solved

# Update table from select statement

Posted on 2011-09-22
379 Views
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
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
• 2
• 2
• 2
• +1

LVL 21

Accepted Solution

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)
``````
0

LVL 60

Assisted Solution

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
;
``````

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

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

LVL 53

Expert Comment

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

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

ID: 36582368
Its 2005
0

LVL 2

Author Closing Comment

ID: 36582577
Both soutions worked.  Thanks again
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
###### Suggested Courses
Course of the Month4 days, 13 hours left to enroll