Why Can't I Use an Alias in a Delete Statement

Why can't I use an alias in a DELETE statement?

To alias the table you'd have to say:

DELETE f FROM dbo.foods AS f WHERE f.name IN (...);

I fail to see the point of aliasing for this specific DELETE statement, especially since (at least IIRC) this no longer conforms to strict ANSI. But yes, as comments suggest, it may be necessary for other query forms (eg correlation).

Can't use ALIAS in delete statement in MARIA DB

You can use a multi-table DELETE statement:

DELETE t1 
FROM `table` t1
JOIN `table` t2 ON t2.col1 = t1.col1 AND t2.col2 = t1.col2 AND t2.id > t1.id

I can´t use alias in sql delete

If you're using aliases... ÿou have to tell what to actually delete (probably this is because table aliases are usually only needed in multiple table syntax... you could just omit the alias altogether):

mysql> delete from tablename r;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'r' at line 1
mysql> delete r from tablename r;
Query OK, 0 rows affected (0.00 sec)

See als the manual:

Note

If you declare an alias for a table, you must use the alias when referring to the table:
DELETE t1 FROM test AS t1, test2 WHERE ...

DELETE FROM `table` AS `alias` ... WHERE `alias`.`column` ... why syntax error?

You can use SQL like this:

DELETE FROM ContactHostCommand 
USING `contact_hostcommands_relation` AS ContactHostCommand 
WHERE (ContactHostCommand.`chr_id` = 999999) 
LIMIT 1

SQLite: Get error when try to delete with alias name

Sqlite does support table aliases with DELETE, you're just using the wrong syntax. You need to use AS between the table name and alias:

sqlite> CREATE TABLE foo(bar);
sqlite> INSERT INTO foo VALUES ('dog');
sqlite> SELECT * FROM foo;
bar       
----------
dog       
sqlite> DELETE FROM foo AS f WHERE f.bar = 'dog';
sqlite> SELECT * FROM foo;
sqlite> 

If you look at the syntax diagrams in the documentation for DELETE, in particular the qualified-table-name one, you'll see that the AS isn't optional like it is in a SELECT table name.

why we provide alias names after 'DELETE' in multiple delete

1. The second is incorrect because if you use an alias, the SQL statement is considered a version of the multi-table DELETE that just happens to have only one table specified. As such, it must follow the other rules of multi-table DELETE. There's no room in the syntax for single-table DELETE to specify an alias.

2. You don't need to have aliases, but you do need to specify which of the tables you are deleting from. So you list the tables, or their aliases, directly after the DELETE keyword. Otherwise, MySQL won't know whether to delete rows only from the first table (with the other tables being present to filter) or to delete from all tables, or some combination.

See: https://dev.mysql.com/doc/refman/5.5/en/delete.html

Table Variables with an Alias in a Delete From Statement

Specify the alias name before FROM statement
Meaning, you are deleting from the aliased table.

delete o1
from   @O as o1
where  ACount = 0 
       and exists ( select  Month 
                    from    @O o2 
                    where   o1.Month = o2.Month 
                            and o2.ACount > 0)

Result

SQLite delete with table alias

The DELETE statement operates on a single table and does not use a table alias. so you will have to use your query as :

DELETE FROM Students WHERE sid=12546

Update:
SQLite apparently doesn't support joins with the delete statement, as you can see on the Syntax diagrams. In short in SQLite, one DELETE command deletes from only one table. So aliasing is of no use

---

Related Topics