deleting data using table prefix

I can run a select to retrieve data using a prefix 'a' for the specific table involved. However when I try to run a delete using the same criteria it fails telling me

Msg 102, Level 15, State 1,.......Line 1

Incorrect syntax near 'a'

The Select statement looks like:

select count(*) from schema.table a where a.customer_id=1234

The Delete looks like:

delete from schema.table a where a.customer_id=1234

What am I doing wrong here? and how can I prefix the table, because the command I want to run is much more complicated than the example above and it needs the prefix

According to BOL there is no alias for delete statement

Thanks

|||

You can use the TSQL extension for the DELETE statement like:

delete from schema.table

from schema.table a

where a.customer_id=1234

Deleted table.

Hi all,

I'm struck, i'm working on a production support by mistake i deleted a table without a transaction, is there anyway i could retrieve the records that got deleted. :(

Thanks in advance.

Arun

restore from backup to another database and copy the records over.

Good luck|||

Is there anyway by which i can retrieve it from transaction log.

|||

Possibly, if you haven't backed-up the transaction log since the deletion and if your database's recovery model is not 'Simple'.

One of these tools should be able to help:

http://www.red-gate.com/products/sql_log_rescue/index.htm

http://www.lumigent.com/products/le_sql.html

For future reference, it's a good idea to execute BEGIN TRANSACTION before making manual corrections to Production data - that way you can always ROLLBACK if your code doesn't behave as expected.

Chris

|||If you can get it from a backup, I would highly recommend that. I know it stinks, but it will be the most straightforward (and the most supported :)