Hello,

How can i use MAX function inside UPDATE query ?
I want to save max AutoInc value from one table in to field of another table:


UPDATE "MyTable" SET NewRecID=MAX(ID) FROM "MyTable","SecondTable" ?
^^^ It's not works

I try to write

UPDATE "MyTable" SET NewRecID=s.ID FROM "MyTable", "SecondTable" s WHERE s.ID IN (SELECT
MAX(ID) FROM "SecondTable");

But it's VERY slow if "SecondTable" contains many records

Maybe you know better way to save LastAutoInc value from one table to another ?
I MUST use UPDATE and CAN'T use SELECT INTO because it's overwrite target table.

Thanx !

"amsdev" <amsdev@smtp.ru> wrote in message
news:58A41243-ACA0-403A-99E3-D263A93A40DA@news.elevatesoft.com...
>
> I try to write
>
> UPDATE "MyTable" SET NewRecID=s.ID FROM "MyTable", "SecondTable" s WHERE
> s.ID IN (SELECT
> MAX(ID) FROM "SecondTable");
>
> But it's VERY slow if "SecondTable" contains many records
>

Save max(id) in a temporary table, and join this table to your update

select max(id) maxid into memory\temp from secondtable;
update mytable set newrecid = maxid from mytable, memory\temp;
drop table memory\temp;

Another option is to use a parameter in the update query, and set that
parameter to secondtable Lastautoinc.

(SQL)
update mytable set newrecid = :maxid from mytable;

(pascal)
Query.ParamByName('MaxID').AsInteger := SecondTable.LastAutoinc - 1; (I
think -1, I don't remember if it is the next to be assigned or the last one
assigned)
Query.ExecSQL;

Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:5825A999-BBAA-4BF5-918B-94DBAA6183BE@news.elevatesoft.com...
>
>
> (SQL)
> update mytable set newrecid = :maxid from mytable;
>

This is wrong delete "from mytable".

Hello,

Can I use

drop table memory\temp

inside Transaction ?

DROP TABLE is not compatible with transactions !

"amsdev" <amsdev@smtp.ru> wrote in message
news:FBFC5C20-F7A2-47D2-8C78-5ADAE558F7F2@news.elevatesoft.com...
> Hello,
>
> Can I use
>
> drop table memory\temp
>
> inside Transaction ?
>
> DROP TABLE is not compatible with transactions !
>

So put it outside the transaction. If you have t rollback your update, you
have bigger problems then leaving a table in memory

Robert

>