Misterius syntax error

[Former Member]

DELETE 
FROM bludata.CursosCfcs cc 
WHERE cc.Cfc_Id = 1300 

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)

[MarkCulp · Accepted solution]

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:

DELETE
  FROM bludata.CursosCfcs
  FROM bludata.CursosCfcs cc
 WHERE cc.Cfc_Id = 1300

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.

[VolkerBarth · Accepted solution]

@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?

[MarkCulp · Accepted solution]

@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?

[VolkerBarth · Accepted solution]

@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.

[VolkerBarth · Accepted solution]

@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.

[MarkCulp · Accepted solution]

@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.

[VolkerBarth · Accepted solution]

@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.

[Former Member]

This appears to be a long-standing omission on our part - even SQL Anywhere 5.5 did not support correlation names on a DELETE statement without the second FROM clause.

This will be supported in the forthcoming SQL Anywhere Innsbruck release.

[former_SQLA_member1694886]

+1 from me for that. It's stumped me on countless occasions.

[VolkerBarth]

So according to Mark's second comment to his own answer: For versions 10/11, you are going to correct the docs that an alias is only valid if a second FROM clause is used, whereas with version 12, this restriction will be taken away and the behaviour will fit the current docs - correct?

[Former Member]

That's right, Volker. We'll change version 12 to support correlation names with DELETE FROM, and for the existing V11 on DCX I'll see what can be done to clarify the supported syntax in that release.

[VolkerBarth]

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

DELETE
FROM bludata.CursosCfcs cc
FROM bludata.CursosCfcs cc
WHERE cc.Cfc_Id = 1300

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

DELETE
FROM bludata.CursosCfcs
WHERE CursosCfcs.Cfc_Id = 1300

or you could omit the column qualification at all as in

DELETE
FROM bludata.CursosCfcs
WHERE Cfc_Id = 1300

But as stated, I don't know whether these simpler forms are adequate for your particular usage.