Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
MAX function inside UPDATE query |
Fri, Dec 22 2006 8:40 AM | Permanent Link |
amsdev | 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 ! |
Fri, Dec 22 2006 9:01 AM | Permanent Link |
"Robert" | "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 |
Fri, Dec 22 2006 9:31 AM | Permanent Link |
"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". |
Fri, Dec 22 2006 9:52 AM | Permanent Link |
amsdev | Hello,
Can I use drop table memory\temp inside Transaction ? DROP TABLE is not compatible with transactions ! |
Fri, Dec 22 2006 10:16 AM | Permanent Link |
"Robert" | "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 > |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |