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

ID : 20428

viewed : 22

Tags : sqlsql-serversql-server-cesql

Top 5 Answer for sql - Why can't I use an alias in a DELETE statement?

vote vote

95

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

vote vote

86

The delete statement has strange syntax. It goes like this:

DELETE f FROM foods f WHERE (f.name IN ('chickens', 'rabbits')) 
vote vote

73

vote vote

62

I'm not 100% on why (actually came here to search for the answer), but this also works, and doesn't require replacing all nan values.

import pandas as pd import numpy as np  df = pd.DataFrame([["foo1"], ["foo2"], ["bar"], [np.nan]], columns=['a'])  newdf = df.loc[df['a'].str.contains('foo') == True] 

Works with or without .loc.

I have no idea why this works, as I understand it when you're indexing with brackets pandas evaluates whatever's inside the bracket as either True or False. I can't tell why making the phrase inside the brackets 'extra boolean' has any effect at all.

vote vote

57

You can also use query method to query the columns of a DataFrame with a boolean expression as follows:

df.query('a.str.contains("foo", na=False)') 

Note you might not get performance improvement, but it is more readable (arguably).

Top 3 video Explaining sql - Why can't I use an alias in a DELETE statement?

Related QUESTION?