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)

asked 06 Apr '10, 20:27

Ismael's gravatar image

Ismael
2026612
accept rate: 50%

edited 07 Apr '10, 16:16

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659


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.

permanent link

answered 06 Apr '10, 21:09

Mark%20Culp's gravatar image

Mark Culp
22.9k9129269
accept rate: 41%

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?

(07 Apr '10, 09:13) Volker Barth

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

(07 Apr '10, 12:17) Mark Culp

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

(07 Apr '10, 12:29) Volker Barth
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.

(07 Apr '10, 12:36) Volker Barth

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

(07 Apr '10, 13:41) Mark Culp

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

(07 Apr '10, 14:02) Volker Barth
More comments hidden
showing 4 of 6 show all flat view

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.

permanent link

answered 08 Apr '10, 22:41

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

1

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

(09 Apr '10, 07:38) Vincent Buck

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?

(09 Apr '10, 07:45) Volker Barth

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.

(09 Apr '10, 12:01) Glenn Paulley

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.

permanent link

answered 07 Apr '10, 14:12

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×85
×28
×21
×10

question asked: 06 Apr '10, 20:27

question was seen: 1,166 times

last updated: 08 Apr '10, 22:41