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.
You are close. You need to list the table(s) to delete from. For instance: