Delete query not working in mysql

Question

I am trying to delete all records from a table called user_enrole.I am using this query

DELETE * FROM user_enrole

I think syntax of my query is not wrong but it is giving me error saying

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM user_enrole' at line 1

I have doubled check my syntax i am not able to figure out what is going wrong can someone point out please.

Is it occuring because of the relationship this table has with use table or what?

"near '*" tells you where your error starts – MISJHA Apr 26 '14 at 18:40 — MISJHA, Apr 26 '14 at 18:40

score 4 · Accepted Answer · answered Apr 26 '14 at 18:38

You don't need to use the asterisk in a delete. Just do DELETE FROM user_enrole to delete all records.

If you want to delete specific records filtered by one or more conditions, you will specify those conditions in the WHERE clause, like so:

DELETE FROM user_enrole
WHERE somecolumn > 1
AND anothercolumn = 'Username'

score 1 · Answer 2 · answered Apr 26 '14 at 18:42

1

When you write SELECT * FROM... the * means everything, i.e. all fields. All parts of the row(s). It makes no sense to use the same syntax when deleting, because you can only delete entire rows. This is why the syntax is DELETE FROM...

answered Apr 26 '14 at 18:42

abl

5,970
4
25
44

score 0 · Answer 3 · edited May 23 '17 at 12:09

0

Better to use "TRUNCATE TABLE user_enroll" as its much much faster due to just deleting all rows. comparison of truncate vs delete in mysql/sqlserver

edited May 23 '17 at 12:09

Community

1
1

answered Apr 26 '14 at 18:41

Thomas

1,401
8
12

score 0 · Answer 4 · answered Oct 25 '15 at 04:16

I am making input to this NOW because I had same problem. When I went tru answers given, it didn't help. Probably because the question didn't give full description of all associated scripts: HTML, Javascript, php etc. Below is the structure of the correct php script I use to DELETE from database table

<?php
$servername = "localhost";
$username = "user";
$password = "0007";
$dbname = "CRCSystems";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
$tblname = test_input($_POST["tblname"]);
$names = test_input($_POST["names"]);
    function test_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}
$sql = "SELECT * FROM $tblname WHERE Names = '$names'";
if (mysqli_query($conn, $sql)) {
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_row($result);
    if ($row) {
        $sql = "DELETE FROM $tblname WHERE Names = '$names'";
        $result = mysqli_query($conn, $sql);
        echo 1;         //record deleted    
    } else {
        echo 0;         //record does not exist 
    }
}
mysqli_close($conn);
?>

Delete query not working in mysql

4 Answers4

Linked

Related