Re: SQL Update confusion

["Jim Essinger" <dilbernator@xxxxxxxxx>]

Elvis,

Thanks - that worked, but performed real slow.  When I used an SQL statement
to create the temp file with Key1 and Max_date, then UPDATE from that temp
file, it ran in less than a minute, for both queries.  The complex query
took at least 3 minutes to run.  This is subjective as I did not have my
stop watch going.

Anyone have an idea why the CTE is limited to the SELECT statement?  It
seems that my example would perform much faster, if allowed.  Create the
temp result once, then use it in the update.  It is also easier for me to
read. It just does not fit my logic as to why not.

Jim

On 5/30/07, Elvis Budimlic <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> Not sure if you can use CTE inside an UPDATE.  Try this instead:
>
> update File2 A set Date01 =
>   (select max_date
>    from
>      (select Key1, max(datefield) as Max_Date
>       from filea
>       where a_code = 'K45'
>       group by Key1
>       order by Key1) as t1
>    where t1.Key1 = A.Key2)
> where exists
>   (select 1
>    from
>      (select Key1, max(datefield) as Max_Date
>       from filea
>       where a_code = 'K45'
>       group by Key1
>       order by Key1) as t1
>    where t1.Key1 = A.Key2)
>
> Elvis
>
> Celebrating 10-Years of SQL Performance Excellence
> http://centerfieldtechnology.com/training.asp
>
> -----Original Message-----
> Subject: SQL Update confusion
>
> OK you SQL gurus;
>
> I have searched for the last couple of hours, but not finding the solution
> I
> want.  I have a file that I want the latest date from, so I am using the
> max() to get the latest date.   I want to update a 2nd file date field
> with
> the results of the first file. I am wanting to do this all in the query,
> and
> not create actual temporary files.
>
> Am I on the right track with the following code?  I am getting and error
> "Keyword UPDATE not expected."  I'm on V5R4.
>
> I have something like this;
>
> with T1 as
> (select Key1, max(datefield) as Max_Date
>   from filea
>   where a_code = 'K45'
>   group by Key1
>   order by Key1)
>
> update File2
>   set Date01 = (select max_date from t1
>                              where Key1 = Key2)
> where exists(select 1 from t1
>                              where Key1 = Key2)
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.