Re: Can I do this in SQL? How?

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

McGovern, Sean wrote:
> But can't you create MYFILE view as
>  
> WITH T1 AS (SELECT...)
>  
> and then update MYFILE ?
>   
I wasn't being very clear.  Got too frazzled with the Windows vs. i thread.

Let me try to be more concise.  What I want is this:

with ORDSELECT as (select ORDERNUM from ORDERS where
-- some complex criteria --),

CUSTSELECT as (select CUSTNUM from CUSTOMERS where
-- some more complex criteria --),

PURGEABLEORDERS as (select ORDERNUM from
ORDSELECT join CUSTSELECT on ORDCUST = CUSTNUM where
-- some final selection criteria --)

update ORDERS set ORDPURGED = 'Y' where
 ORDERNUM in (select ORDERNUM from PURGEABLEORDERS)

Now I'm sure with enough work I could get all the criteria for the 
ORDSELECT and CUSTSELECT and PURGEABLEORDERS subselects into the update 
statement, but it would be a mess and more importantly, almost 
impossible to debug.  Instead, with this I can test each subselect to 
make sure it works as intended and then finally put it all together.  
And I do this all the time on complex queries.  However, the problem is 
that the UPDATE statement is not allowed after the definition of the 
CTEs; all I can do is a final SELECT.

And I'm not sure why it is that way, but that's the way it is.

Joe