#8 Possible bad UPDATE sql generation

[Lon Varscsak]

The default UPDATE sql is generated as follows:

UPDATE company t0 SET t0.company='ddd' WHERE ((t0._rowid =
7) AND (t0.company = 'test'))

It uses a table alias in the table name for the UPDATE...which
doesn't appear to be correct. Everything that I can find says that
that's invalid, although I think it works for OpenBase.

If table aliases are required (which I don't think they are because sql
can only update one table, and it never seems to join to other tables)
the sql should be generated as:

UPDATE company SET t0.company='asdfasdf' FROM company t0
WHERE ((t0._rowid = 2) AND (t0.company = 'test'))

we add a FROM clause and use the table aliases there.

--Lon

[Ashley Clark]

Logged In: YES
user_id=1311782

I'm not sure about aliases but the Postgres documentation has this to say
about the FROM clause:

fromlist
A list of table expressions, allowing columns from other tables to appear in
the WHERE condition and the update expressions. This is similar to the
list of tables that can be specified in the FROM Clause of a SELECT
statement. Note that the target table must not appear in the fromlist,
unless you intend a self-join (in which case it must appear with an alias in
the fromlist).... the FROM clause is a PostgreSQL extension

[Lon Varscsak]

Logged In: YES
user_id=1321043

Agreed, it shouldn't have the FROM clause unless needed to join to other
entities and then it should use aliases. I think this will work across lots of
databases too.

[Mont Rothstein]

Logged In: YES
user_id=1162756

This appears to be specific to the OpenBase adaptor, the PostgreSQL
adaptor does not do this.

I don't have OpenBase and haven't used it in *years*.

Can you verify if this is correct syntax for OpenBase or simply allowed?

Thanks,
-Mont