asked on

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.

ASKER CERTIFIED SOLUTION

JestersGrind

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

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.

HainKurt

maybe this:

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

JestersGrind

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

chrisryhal

ASKER

Its 2005

chrisryhal

ASKER

Both soutions worked. Thanks again