Delete rows in MySQL database table based on column parameter

Question

I have JSON data continuously inserted into a DB table but there are some rows which I need to delete concurrently based on the column value. For eg., I need to delete the rows which have a column value (time_min) of more than 90 minutes.

ID    time_min
1       50
2       30
3       91
4       40
5       93
6       95
7       40

Here I need to delete rows containing values more than 90 in time_min column.

This is the current code I'm using.

if($time_min >= 91){
        $sql = "truncate table_name";
        if ($conn->query($sql) === TRUE) {
            echo "Rows Dropped";
        }
    }

It clearly is deleting the entire table values which mess up in the loading of new values. I would just want the rows that have column value of more than 90 in time_min (column name) to be deleted.

Would appreciate if someone can guide me on this.

It would probably help if you read a little of the MySQL manual — RiggsFolly, Feb 16 '18 at 10:27
`truncate` will empty the entire table you need to use the `delete` function — Masivuye Cokile, Feb 16 '18 at 10:28
https://stackoverflow.com/questions/139630/whats-the-difference-between-truncate-and-delete-in-sql — Masivuye Cokile, Feb 16 '18 at 10:31

score 1 · Answer 1 · answered Feb 16 '18 at 10:25

1

You could use a where condition for check the 90 time_min for delete

  delete from my_table 
  where time_min  > 90

answered Feb 16 '18 at 10:25

ScaisEdge

129,293
10
87
97

score 1 · Answer 2 · edited Feb 16 '18 at 10:27

1

try this-

 DELETE FROM `table_name` where table_name.time_min>90

edited Feb 16 '18 at 10:27

Masivuye Cokile

4,729
3
18
34

answered Feb 16 '18 at 10:26

coder001

112
7

score 0 · Answer 3 · edited Feb 16 '18 at 13:38

0

Use WHERE Clause condition on column in the query

DELETE FROM 'table_name' tbn WHERE tbn.time_min>90

edited Feb 16 '18 at 13:38

Rahul

1,587
1
8
18

answered Feb 16 '18 at 10:30

phpforcoders

316
1
9

Delete rows in MySQL database table based on column parameter

3 Answers3