Subject Re: [IB-Architect]
Author Helen Borrie
At 11:26 AM 10-02-01 -0300, you wrote:
>Some like this ?
>UPDATE TableIWantUpdate
>SET FieldIWant = ct.FieldIWant + at.AddedValue
>FROM CopyofTableIWantUpdate ct , AnotherTable at
>WHERE ct.KeyField = at.ForeignKey
>I don´t know how others dbms handle this, but copying syntax from another dbms will make user more comfortable when migrating to Firebird. Or not ?

I would hope ANY RDBMS would barf on this statement. There is nothing there to indicate any relationship between the columns being read and the column being updated. A valid statement in standard SQL would be

UPDATE TableIWantUpdate
SET FieldIWant =
(SELECT ct.FieldIWant + at.AddedValue
FROM CopyofTableIWantUpdate ct
JOIN AnotherTable at
ON ct.KeyField = at.ForeignKey
WHERE ct.KeyField = TableIWantUpdate.KeyField);

What general rule could be applied to decide what is "more comfortable" than standard SQL (other than dropping SQL altogether, of course)?. It's a retrograde step to change from standard SQL syntax to match some other vendor's non-standard syntax. Why would you do it? How would you decide which non-standard syntax to use?

The approach of adding a new command is preferable, if it is not supported by the standard.


All for Open and Open for All
InterBase Developer Initiative ·