Hi Fabio,

If the literal after DELETE keyword doesn't match alias for any table in the FROM it is treated as a separate table/cursor. Same for UPDATE command.

The following query joins three tables table1 as table1, table1 as T and joinData as joinData, it deletes records from table1 as table1:

DELETE table1 FROM table1 T join joinData ON T.f2=joinData.fr

This query joins two tables table1 as T and joinData as joinData, it deletes records from table1 as T:

DELETE T FROM table1 T join joinData ON T.f2=joinData.fr

Thanks,
Aleksey.



>>Hi Fabio,
>>
>>The result is correct, please read documentation.
>>
>>Thanks,
>>Aleksey.
>
>I read, and don't find where is written that
>the FROM clause is a tables product.
>Know you other database engines where this happen ?
>Sure MS SQL not!
>
>Why to implement this ambiguous syntax ?
>It does not have some advantage, only disadvantages!
>
>
>Fabio
>
>>
>>>The new SQL Language Improvements are not implemented correctly.
>>>
>>>Repro:
>>>

>>>CLEAR
>>>
>>>CREATE CURSOR joindata (fr i)
>>>INSERT INTO joinData VALUES (1)
>>>
>>>CREATE CURSOR table1 (f2 i)
>>>INSERT INTO table1 VALUES (1)
>>>INSERT INTO table1 VALUES (2)
>>>INSERT INTO table1 VALUES (3)
>>>INSERT INTO table1 VALUES (4)
>>>
>>>INDEX ON f2 TAG tf2
>>>
>>>SET DELETED OFF
>>>SYS(3054,11)
>>>
>>>* result is correct
>>>DELETE table1 FROM table1 join joinData ON table1.f2=joinData.fr
>>>? _tally
>>>BROWSE
>>>RECALL ALL IN table1
>>>
>>>* Result is wrong, table1 is not filtered and all records are deleted
>>>DELETE table1 FROM table1 T join joinData ON T.f2=joinData.fr
>>>? _tally
>>>BROWSE
>>>RECALL ALL IN table1
>>>
>>>* expected a compile Error
>>>* the where correlate the two table1, and fix the issue
>>>DELETE table1 FROM table1 T join joinData ON t.f2=joinData.fr where T.f2=table1.f2
>>>? _tally
>>>BROWSE
>>>RECALL ALL IN table1
>>>
>>>* expected a compile Error, the command is ambiguous
>>>DELETE table1 FROM table1 T join joinData ON table1.f2=joinData.fr where T.f2=table1.f2
>>>? _tally
>>>BROWSE
>>>

>>>
>>>I guess that UPDATE SQL have the same bug.
>>>
>>>Fabio