Re: Remote Update (error -4425)

>From: senyem@malitnet.net.my >Date: Thu, 27 Feb 1997 23:45:40 +0800 >X-Informix-List-Id: <list.13342> > >The statement below doesn't works: > > UPDATE database@server-name:table-name > SET database@server-name:table-name.* = tmp_table-name.* > > | The variable "tmp_table-name" has not been defined > | LIKE the table "database@server-name:table-name". > | See error number -4425. > >However, if I apply the same convention during INSERTion, it works: > > INSERT INTO database@server-name:table-name VALUES (tmp_table-name.*)> >Why doesn't the system allowed for such UPDATE when the tmp_table-name >have been defined accordingly. Nothing is as simple as it seems. First of all, DB-Access and plain ESQL/C do not allow the notation you use. You cannot cite the table name on the LHS of the SET clause -- try it and you get a -201 error. You can specify '*' as the list of column names. OK - so far, so bad. However, once upon a time it was documented in the I4GL manuals that you could write what you wrote, so the I4GL compiler tries to fix the SQL for you so it will work. To do so, it expands the LHS by scanning the database schema (at compile time) for the columns in the named table (so it will fail if the table doesn't exist at compile time!) and checks that the declaration of the variable on the RHS matches exactly the table (by insisting that it is a record declared like the table). And then, if it accepts what you wrote, it rewrites your UPDATE statement in ESQL/C as: $ UPDATE database@server:tablename SET (col1, col2, col3, ...) = ($tmp_tablename.col1, $tmp_tablename.col2, $tmp_tablename.col3, ...); If you wrote your UPDATE statement as shown below, everything would work OK anyway: UPDATE database@server:tablename SET * = (tmp_tablename.*) This gets translated to: $ UPDATE database@server:tablename SET * = ($tmp_tablename.col1, $tmp_tablename.col2, $tmp_tablename.col3, ...); By contrast, with the INSERT statement, all the compiler does is translate the statement to: $ INSERT INTO database@server:tablename VALUES ($tmp_tablename.col1, $tmp_tablename.col2, $tmp_tablename.col3, ...); The difference is that it does not have to do anything fancy in the way of examining the table schema at compile time. All this analysis assumes that your 'tmp_table-name.*' is a program record. If you are looking to update one table from the contents of a temporary table, then you have to use a completely different notation, and you have to decide what the exact semantics of the update should be. Probably what you are after is the update of any records where the primary key in the temp table matches the primary key in the primary table, with insertion of any records in the temp table but not in the primary table. This is tricky: UPDATE PrimaryTable SET (NonPKCol1, NonPKCol2, ...) = ((SELECT NonPKCol1, NonPKCol2, ... FROM TempTable WHERE PrimaryTable.PKCol = TempTable.PKCol)) WHERE PKCOl in (SELECT PKCol FROM TempTable); SELECT PKCol FROM PrimaryTable INTO TEMP PrimaryPKCols WITH NO LOG; INSERT INTO PrimaryTable SELECT * FROM TempTable WHERE PKCol NOT IN (SELECT PkCol FROM PrimaryPKCols); Yes, you do need the extra temp table, unless you decide to do: DELETE FROM TempTable WHERE PKCol IN (SELECT PKCol FROM PrimaryTable); INSERT INTO PrimaryTable SELECT * FROM TempTable; This will work if you don't have any further need for the contents of TempTable. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: Admission - I didn't formally go and check every statement I made above today, so the translations may differ mildly from what I state above, but the gist is correct. One trivial difference is that I use upper-case for ESQL/C keywords but I4GL does not. PPS: And no, I don't think it is a particularly useful algorithm that is used. I'd want to make sure that the table name on the LHS of the SET clause is the same as in the UPDATE clause, and then I'd simply eliminate it -- no compile time checking on what the '*' means. But I'm lazy!