SQL SELECT statement doesn't work for DELETE

Question

I have an SQL statement that I am trying to run to delete an answer associated with a user_id and a username. The query works when trying to select, but doesn't work for delete. The error that I get on the DELETE statement is as follows:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS ans INNER JOIN users AS usr ON ans.user_id = usr.user_id WHERE (usr.userna...' at line 1
Query is: DELETE FROM answers AS ans INNER JOIN users AS usr ON ans.user_id = usr.user_id WHERE (usr.username = 'antonios')

The output I get from the SELECT statement:

1   answer text goes here   1   1   a   12  44  44  antonios    U4HhMNnasQB1919QduzHO9+tgWoxsfqMY4MDUwYDDqQ=    $argon2id$v=19$m=125000,t=4,p=1$gE6rtZdzpBaehzlyrVreRQ$WuhSeAaXE2bX3N4/k1yu3yQm9p7UdKGeCU/vmYn+M7c  0   0   0   0   0   2020-10-14

The SELECT statement:

SELECT * FROM answers AS ans INNER JOIN users AS usr ON ans.user_id = usr.user_id WHERE (usr.username = 'username');

The DELETE statement:

DELETE FROM answers AS ans INNER JOIN users AS usr ON ans.user_id = usr.user_id WHERE (usr.username = 'username');

table structure for users:

user_id             int(11) NO                      PRIMARY_KEY               auto_increment
username            varchar(40)         NOT_NULL    MUL_KEY     
email               varchar(128)        NOT_NULL            
password            char(98)            NOT_NULL            
userScore           int(10) unsigned    NOT_NULL                  DEFAULT: 0    
questionsAnswered   int(10) unsigned    NOT_NULL                  DEFAULT: 0    
highestAnswerStreak int(10) unsigned    NOT_NULL                  DEFAULT: 0    
correctAnswers      int(10) unsigned    NOT_NULL                  DEFAULT: 0    
wrongAnswers        int(10) unsigned    NOT_NULL                  DEFAULT: 0    
activeSince date    NOT_NULL

table structure for answers:

answers_id      int(11)     NOT_NULL   PRIMARY_KEY   auto_increment
answerText      text        NOT_NULL            
question_id     int(11)     NOT_NULL            
isCorrect       tinyint(4)  NOT_NULL            
choiceLetter    varchar(1)  NOT_NULL            
questionNumber  smallint(6) NOT_NULL            
user_id int(11) NOT_NULL

The expected output for the DELETE statement would be affecting one or multiple rows in the answers table (all rows assigned to a user_id obtained from the username in the WHERE clause) by deletion.

Gordon Linoff · Accepted Answer · 2020-10-15 16:26:13Z

1

You are close. You need to list the table(s) to delete from. For instance:

DELETE ans
    FROM answers ans INNER JOIN
         users usr
         ON ans.user_id = usr.user_id
    WHERE usr.username = 'username';

answered Oct 15, 2020 at 16:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Revvz Over a year ago

That did the trick. Thanks for the quick reply! I'll accept when SO lets me.

Collectives™ on Stack Overflow

SQL SELECT statement doesn't work for DELETE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related