OperationalError: near "u": syntax error <- while trying to delete rows from 2 columns inner jointed

Question

I am trying to delete rows from two tables with inner join. I don't really understand why this error pops up.

import sqlite3
login = 'uzytkownik6'
conn = sqlite3.connect('fiszki.db')    
c = conn.cursor()
c.execute("DELETE u.*, t.* FROM users u INNER JOIN translations t ON 
u.user_id=t.user_id WHERE u.user_name='{}'".format(login)) 
conn.commit()

But I get error:

OperationalError: near "u": syntax error

Shouldn't there be an AS in there? Also you shouldn't use string interpolation to put values in queries like that. — jonrsharpe
– jonrsharpe, Commented Jan 18, 2019 at 22:40
I has other codes with select where 'as' wasn't needed, but I checked it added 'as' and it didn't change the situation, still the same problem — Hub_Ert
– Hub_Ert, Commented Jan 18, 2019 at 22:42
Why do you think you can delete from multiple tables with one statement, or individual columns? Sqlite DELETE syntax: sqlite.org/lang_delete.html — Shawn
– Shawn, Commented Jan 18, 2019 at 23:11
Possible duplicate of How delete table inner join with other table in Sqlite? — manveti
– manveti, Commented Jan 18, 2019 at 23:32

mohshbool · Accepted Answer · 2019-01-18 22:49:02Z

0

You should never use the normal python string formatting when executing SQL commands. Example: db.execute("DELETE FROM users WHERE userId = (?)", [userId]). Also, you don't really need to have run the db.cursor() method after connecting. See SQLite3 API documentation for Python 3.

answered Jan 18, 2019 at 22:49

mohshbool

412 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hub_Ert Over a year ago

Thanks, wasn't aware of that, it worked in some other simple query. However when I changed it to

c.execute("DELETE u.*, t.* FROM users as u INNER JOIN translations as t ON u.user_id=t.user_id WHERE u.user_name=(?)", login)

, I still get the same problem -> OperationalError: near "u": syntax error

David M Over a year ago

This answer isn't quite answering the question. It is better to give an answer that solves the observed problem, and then point out a superior approach. Also, if you include in your answer why you should never use the normal Python string formatting when executing SQL commands, that makes your answer much better.

mohshbool Over a year ago

@DavidM, Thanks for reminding me of that, I'm still new to Stackoverflow. I will make sure to include it in future answers and focus more on the question. Also, Hub_Ert, I don't exactly know what is the fix to your specific questions, but my guess would be that the problem would be that you're trying to use a DELETE statement with an alias and a join, which isn't possible, see this answer.

mohshbool Over a year ago

And as for why you shouldn't use native python string formatting while executing SQL statements, is the vulnerability to SQL Injection Attacks.

Collectives™ on Stack Overflow

OperationalError: near "u": syntax error <- while trying to delete rows from 2 columns inner jointed

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related