It gives me syntax error on line 3. I read the manual and the aliases 'cc' is fine. I'm using sql anywhere 11. (I have a misterious reason to use this 'cc' alias) |
|
The (first) FROM clause of the DELETE statement is not allowed to have an alias. See http://dcx.sybase.com/index.html#1101en/dbreference_en11/delete-statement.html If you want to use an alias, you need to add a second FROM clause. Example: This is explained in the above doc page. - Basically the first FROM clause specifies which table is going to be deleted from, and if you need to use a complex condition (including using an alias) then you add another FROM clause and specify your complex condition. 1
@Mark: I confess that I wondered if this is true as I regularly use similar UPDATE statements with a FROM clause and use an alias for the table to update, i.e. something like "UPDATE table1 t1 SET col1 = x FROM table1 t1 key join table2 t2 WHERE ...". As to the 11.0.1 docs, the first alias "t1" is invalid syntax, too, but with all versions of SA 10/11 I have used (currently 11.0.1.2376), that syntax has always worked. Is this an oversight? @Volker: The 11.0.1 docs - see http://dcx.sybase.com/index.html#1101en/dbreference_en11/update-statement.html - indicate that an alias is allowed in an UPDATE statement (a 'table-name' is allowed a correlation name). I do not see an oversight. Did I misundertand your question? @Mark: You are right, of course. I have overseen the "table-name" definition and was stumbled by the paragraph "This is allowed as an exception to the general rule..." in the "FROM clause" explanation that seems to be similar to the one for the DELETE statement. So the UPDATE syntax works as expected. Comment Text Removed
@Mark: However, then I think the DELETE syntax as of the 11.0.1 docs should allow an alias for the table to delete from, too: According to the syntax, it's a "table-expression", and following that definition (explained on the "FROM clause" page, cf. http://dcx.sybase.com/1101en/dbreference_en11/from-statement.html), an alias is allowed. @Volker: You are correct - I looked at the grammar - you are allowed to give a correlation name on the table in the first FROM clause if you also use a second FROM clause with a table expression. This is done so that you can differentiate between the table named in the first FROM clause from the same table named in the second FROM clause (amoung other things). If a single FROM clause is used, you cannot give a correlation name. This is not clear from the docs... I will see what can be done to improve the documentation. @Mark: Thanks for the clarification (and I guess I already haved used DELETEs with both FROM clauses and aliases, too) - and I agree that an alias is just not useful when DELETE is used without the second FROM clause. More comments hidden
|
|
Just an addition to Mark's answer, based on the following discussion (cf. the comments to his answer): If you need to use an alias, the second FROM clause is necessary. However, then it's alright (and more comprehensible IMHO) to use the same alias in both FROM clauses, as in I don't know why you need an alias (or need to qualify the column name). Besides an alias, you might just use the table name itself to qualify (and then not need to use a second FROM clause) as in or you could omit the column qualification at all as in But as stated, I don't know whether these simpler forms are adequate for your particular usage. |