Re: SQL Update confusion

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.