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
– MISJHA

2014-04-26 18:40:41 +00:00
Commented Apr 26, 2014 at 18:40 — MISJHA
– MISJHA, Commented Apr 26, 2014 at 18:40

shree.pat18 · Accepted Answer · 2014-04-26 18:38:15Z

5

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'

answered Apr 26, 2014 at 18:38

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

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

Comments

abl · Accepted Answer · 2014-04-26 18:42:32Z

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, 2014 at 18:42

abl

5,9684 gold badges27 silver badges44 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:09:14Z

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, 2017 at 12:09

CommunityBot

11 silver badge

answered Apr 26, 2014 at 18:41

Thomas

1,4018 silver badges12 bronze badges

Comments

Olu Adabonyan · Accepted Answer · 2015-10-25 04:16:58Z

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);
?>

Collectives™ on Stack Overflow

Delete query not working in mysql

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related