0

I'm trying to turn this select statement into a delete statement and I'm struggling to not delete all the records in the table. Here is the SQL select statement I'm using

SELECT  *
    FROM  message_cus_board mcb
    WHERE  mcb.board_id IN (
        SELECT  mb.board_id
            FROM  message_boards mb
            WHERE  mb.customer_id = :customerid
              AND  mb.universal_board = 'Y'
                          )
    ORDER BY  mcb.created_on DESC"

This queries all of the messages from the message_cus_board table and basically I want to delete all of the messages this would normally display except the last 15. One thing I've tried is taking all of those message_id's and making an array of the most recent 15 then creating a delete statement with those id's like so ->

"DELETE FROM message_cus_board WHERE message_id NOT IN ($string_of_ids)"

The only issue is that this delete query gets rid of ALL other messages, not just the ones associated with the right customer. I'm struggling to create a delete query that limits the delete to the right customer using similar logic to the select statement above. I'm sure it's a fairly simple solution but it's currently eluding me. Thanks for any help!

2
  • 1
    Always provide you version of MySQL! Also, the structure of your table message_cus_board (SHOW CREATE TABLE message_cus_board\G). Ideally, some sample data (perhaps in the form of a fiddle) would also be good! Mar 16 '20 at 19:24
  • Use a multi-table DELETE
    – Rick James
    Mar 30 '20 at 0:45
0

You can just use the select statement in the where clause and just select for the element you are matching on

DELETE FROM message_cus_board 
WHERE message_id NOT IN (
    SELECT message_id 
    FROM message_cus_board mcb
    WHERE mcb.board_id IN 
        (SELECT mb.board_id 
         FROM message_boards mb 
         WHERE mb.customer_id = :customerid 
         AND mb.universal_board = 'Y'))
4
  • I'm getting this error -> You can't specify target table 'message_cus_board' for update in FROM clause in... Mar 16 '20 at 16:07
  • @motelonesun I am guessing that the version of mysql you are on does not allow this setup which is unfortunate.
    – Joe W
    Mar 16 '20 at 16:16
  • Oh well! I appreciate the help :D Mar 16 '20 at 16:22
  • @Joe W, you pretty much have it except for just a minor change. See here codeproject.com/Tips/831164/…. You can probably make the whole select a "with" statement and then do your delete. Essential point is to hide the table name so that MySQL doesn't realize that the same table you are updating isn't the same as the table you are selecting from.
    – Chuma
    Apr 1 '21 at 0:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.