Delete All Rows in a Table Based on Another Table

Delete all rows in a table based on another table

DELETE Table1
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ID

How to delete records in one table based on the values in another table?

delete 
from table1
where cm_id IN (select um_id from table2 where order_num between 518 and 520)

SQL Delete Rows Based on Another Table

I think this is what you want:

DELETE FROM `table1`
WHERE `group` in (SELECT DISTINCT `group` FROM `table2`)

How to delete records in one table based on the values in another table

Not quite clear about the question. Sounds like: remove all wish_list entries for books that have quantity 0 in the book table?

DELETE FROM wish_list
  WHERE isbn IN ( SELECT isbn
                    FROM book
                    WHERE quantity = 0 );

Or the update:

UPDATE wish_list
  SET quantity = -1
  WHERE isbn IN ( SELECT isbn
                    FROM book
                    WHERE quantity = 0 );

Update: Deleting duplicates from wish_list (regardless of quantity). Please test, I haven't:

DELETE FROM wish_list
  WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM wish_list GROUP BY isbn);

Delete rows depending on values from another table

You were very close. I suggest you use NOT IN instead of IN:

DELETE FROM Customer 
  WHERE CustID = $1 AND
        CustID NOT IN (SELECT DISTINCT CustID
                         FROM Order
                         WHERE Status = 'A');

I'm guessing here that Status = 'A' means "active order". If it's something else change the code appropriately.

MySQL ... Delete Records From a Table Based on Query of Another Table

One possible explanation for the slow delete is that takes some time for MySQL to lookup each slice_name in the complete_set table against the values in the subquery. We can try speeding this up as follows. First, create a new table to replace the subquery, which will serve as a materialized view:

CREATE TEMPORARY TABLE changes_view
(PRIMARY KEY pkey (slice_name))
SELECT slice_name
FROM changes
GROUP BY slice_name
HAVING COUNT(slice_name) > 1;

Now phrase your delete using a join:

DELETE t1
FROM complete_set t1
INNER JOIN changes_view t2
    ON t1.slice_name = t2.slice_name;

The (intended) trick here is that the delete join should run fast because MySQL can quickly lookup a slice_name value in the complete_set table against the materialized view table, since the latter has an index on slice_name.

BigQuery - Delete based on values from another table

Try to move your join logic to an exists clause

delete from `datapool-prt-supplychain-wrk.TRANSPORTS_DATA.tbl_historico_tb_rota` AS a 
where exists (
    select 1 
    from `datapool-prt-supplychain-wrk.TRANSPORTS_DATA.tbl_input_tb_rota` b
    where
      CAST(  
        CONCAT(
          RIGHT(b.data,4),'-',
          CASE 
            WHEN LENGTH(b.data) = 8 THEN 
              CONCAT('0',SUBSTR(b.data ,3,1),'-0',LEFT(b.data ,1))
            WHEN LENGTH(b.data) = 9 AND SUBSTR(b.data,3,1) = "-" THEN 
              CONCAT('0',SUBSTR(b.data ,4,1),"-",LEFT(b.data ,2))
            WHEN LENGTH(b.data) = 9 THEN 
              CONCAT(SUBSTR(b.data ,3,2),'-0',LEFT(b.data ,1))
            WHEN LENGTH(b.data) = 10 THEN 
              CONCAT(SUBSTR(b.data,4,2),'-',LEFT(b.data,2))
            ELSE '1900-01-01'
          END
        )
      AS DATE ) = a.data
      )

How to delete rows from one table matching another table?

A simple INNER JOIN should do the job:

DELETE T1
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.postid = T2.postid
AND T1.userid = T2.userid

---

Related Topics