How to Delete Last Record(On Condition) from a Table in MySQL

How to delete last record(on condition) from a table in MySql

You need to filter the table by user_id (eg WHERE user_id=1), then sort it by time (eg ORDER BY datetime) and then limit the query to just one item (eg LIMIT 1) and you delete the result of this query. At the end youl get query like this:

DELETE FROM LoginTime WHERE user_id=1 ORDER BY datetime DESC LIMIT 1

delete the last row in a table using sql query?

If id is auto-increment then you can use the following

delete from marks
order by id desc limit 1

How to delete unknown number last record (on condition)?

It seems that you want to delete the last set of records where all the values are 0. This is a bit of a pain. You can find the minimum such id as:

select min(t.id)
from table t
where t.emailid = 0 and
      not exists (select 1 from table t2 where t2.id > t.id and t2.emailid <> 0);

The logic is: find all rows where emailid is 0 and there are no subsequent emailids that are not zero.

You can put this into a delete using join:

delete t
    from table t cross join
         (select min(t.id) as first0id
          from table t
          where t.emailid = 0 and
                not exists (select 1 from table t2 where t2.id > t.id and t2.emailid <> 0)
         ) tmin
     where t.id >= tmin.first0id;

How to delete last row from a table without conditions?

Tables represent unordered sets. There is no ordering to the table, so there is no "first" row and there is no "last" row. There is no "right order". That is simply how SQL works.

In order to define a "last" row, you need a column that specifies the ordering. Often, this will be an auto-incremented id, so the last row would have the largest id.

If you have such a column and are using MySQL then you can use limit and order by with delete:

delete t from <table> t
    order by t.id desc
    limit 1;

If you do not have such a column, you will need to identify the row you want to delete through other means:

delete t from <table> t
    where col1 = ? and col2 = ? and . . .;

Delete record based on condition in parent table

You can use the following solution using INNER JOIN:

DELETE c.* FROM child_object c 
    INNER JOIN parent_object p ON c.parent_id = p.id  
WHERE c.id = 3 AND p.moderator_id = 2

demo: http://sqlfiddle.com/#!9/7290b5/1/0

How to delete record from table if its count 5

If you do not always have 8 records and want to select the last 3 records from the table where systemid=1 however many records there are, then a good way to do this is to use the IN selector in your SQL statement.

It would be good is you could do this simply using the statement

SELECT * FROM mytable WHERE id IN (SELECT id FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3)

However this is not yet supported in MySQL and if you try this then you will get an error like

...doesn't yet support 'LIMIT & IN/ALL/SOME subquery'

So you need a workaround as follows (using SELECT to test):

SET @myvar := (SELECT GROUP_CONCAT(id SEPARATOR ',') AS myval FROM (SELECT * FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3 ) A GROUP BY A.systemid);
SELECT * FROM mytable WHERE FIND_IN_SET(id,@myvar);

The way that this works (first line) is to set a variable called @myvar which will hold the last 3 values as a comma separated string if id values. In your case

9,8,10

Then select the rows where the 'id' is in this string.

Replace the 'SELECT *' with 'DELETE FROM' to finalize the result so your query will be

SET @myvar := (SELECT GROUP_CONCAT(id SEPARATOR ',') AS myval FROM (SELECT * FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3 ) A GROUP BY A.systemid);
DELETE FROM mytable WHERE NOT FIND_IN_SET(id,@myvar);

I hope that this helps.

Delete Rows Where Condition is Met? MySQL

You want to find the records which don't have a Last Purchase Date, because those are the ones you will be deleting. So...

SELECT t1.customerID, t1.`Last Purchase Date` 
FROM `table 2` t1
WHERE
    t1.`Last Purchase Date` = ''
    AND EXISTS (
            SELECT t2.customerID
            FROM `table 2` t2
            WHERE
                t2.customerID = t1.customerID
                AND t2.`Last Purchase Date` <> ''
        )
ORDER BY t1.customerID DESC;

For deletion, you would simply remove the ORDER BY clause, and replace SELECT ... FROM with DELETE FROM.

Delete records from table, if it exceeds 100k records

You could wrap into another select the subquery;

DELETE FROM  audit_logs 
WHERE  id not in  (SELECT t1.id 
                   FROM ( SELECT id 
                          FROM audit_logs 
                          ORDER BY ID DESC 
                          LIMIT 100000
                         ) as t1
                    );
                

Or use NOT EXISTS :

    DELETE FROM  audit_logs  a1 
    WHERE  NOT EXISTS   ( SELECT * 
                          FROM ( SELECT id 
                                 FROM audit_logs a2
                                 ORDER BY ID DESC 
                                 LIMIT 100000
                               ) as t1
                         );

Read more on : https://dev.mysql.com/doc/refman/8.0/en/update.html

---

Related Topics