How to Write a SQL Delete Statement with a Select Statement in the Where Clause

How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?

You need to identify the primary key in TableA in order to delete the correct record. The primary key may be a single column or a combination of several columns that uniquely identifies a row in the table. If there is no primary key, then the ROWID pseudo column may be used as the primary key.

DELETE FROM tableA
WHERE ROWID IN 
  ( SELECT q.ROWID
    FROM tableA q
      INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) 
    WHERE (LENGTH(q.memotext) NOT IN (8,9,10) OR q.memotext NOT LIKE '%/%/%')
      AND (u.FldFormat = 'Date'));

Delete Query inside Where clause

Yes. They can run a delete. They can type:

 1 = 1; DELETE FROM MY_TABLE;

Or even worse in some ways, (since you should have backups):

1 = 0 UNION SELECT SOCIAL_SECURITY_NUMBER, CREDIT_CARD_NUMBER, OTHER_SENSITIVE_DATA FROM MY_SENSITIVE_TABLE;

Now, in your case its hard to validate. Normally if you are just passing a value to filter on you can use parameterised sql to save yourself. You however also need to let the user select a column. In cases like these, usually we use a drop down to allow the user to select a predefined list of columns and then validate the column name server side. We give the user a text box to enter the value to match and then parameterise that.

Delete query with select query is not working as expected

You should use some column on which basis you want to delete the record from table B_786, like if there are 2 columns in table B_786 (id name) and having 700k records, and there are
100k records I_786 with columns (id, name). So to delete the data from B_786 table which matches the record with I_786 table.

Delete from B_786 where id in (select id from I_786);

By executing above command data will be deleted from B_786 which matches the id from I_786.

delete records which are selected from select query on the same table

Your query would look something like

delete from emp 
from  emp e
where exists (select * 
              from EMP 
              WHERE e.EMPNAME = EMPNAME
               AND  e.EMPSALARY = EMPSALARY 
               AND  <another Condition>)   --<-- Condition on which you want to delete rows

How to delete entries from a SELECT query result in mysql?

If we have a complex query that returns the id value of rows in wp_posts that we want to delete (assuming that id is the primary key or a unique key of a row in the table)... as an example

SELECT p.id
  FROM wp_posts p
  JOIN wp_icl_translations t
    ON t.element_id = p.id 
 WHERE t.language_code = 'es-es'
   AND t.element_type  = 'post_product'
   AND p.post_type     = 'product'
 GROUP
    BY p.id
    

We can then use that query as an inline view. We wrap the query in parens and reference it in the FROM clause of another query. MySQL requires that we assign an alias to thhe inline view (or derived table in the MySQL vernacular).

We can join the result from the inline view that back to the table we want to remove rows from. We write this a SELECT statement first

SELECT r.*
  FROM ( -- inline view
         SELECT p.id
           FROM wp_posts p
           JOIN wp_icl_translations t
             ON t.element_id = p.id 
          WHERE t.language_code = 'es-es'
            AND t.element_type  = 'post_product'
            AND p.post_type     = 'product'
          GROUP
             BY p.id
       ) q
  JOIN wp_posts r
    ON r.id = q.id
    

to return the set of rows to be removed. We can verify that this is the intended set, or insert (create table as) the set of rows into backup...

Once we are confident that the SELECT is returning the rows we want to remove, we can convert it into a DELETE statement by replacing the SELECT keyword with DELETE.

DELETE r.*
  FROM ( -- inline view
         SELECT p.id
           FROM wp_posts p
           JOIN wp_icl_translations t
             ON t.element_id = p.id 
          WHERE t.language_code = 'es-es'
            AND t.element_type  = 'post_product'
            AND p.post_type     = 'product'
          GROUP
             BY p.id
       ) q
  JOIN wp_posts r
    ON r.id = q.id

Delete All Data Found With Query

One way would be using an INNER JOIN:

DELETE A
FROM Temp_A A
INNER JOIN (SELECT Order_ID, Part_Number, COUNT(*)
            FROM Temp_A
            GROUP BY Order_ID, Part_Number
            HAVING COUNT(*) > 1) B
    ON A.Order_ID = B.Order_ID
    AND A.Part_Number = B.Part_Number
;

If you are using SQL Server 2005+, then you can use a CTE:

WITH CTE AS
(
    SELECT  *,
            N = COUNT(*) OVER(PARTITION BY OrderID, Part_Number)
    FROM Temp_A
)
DELETE FROM CTE
WHERE N > 1
;

Create DELETE query by SELECTING from table

Hi if i understand all what you're trying i think this can respond :

SET NOCOUNT ON;  

DECLARE  @query varchar(4000);  

PRINT '-------- Deleting rows --------';  

DECLARE deleting_cursor CURSOR FOR   
SELECT 'DELETE FROM ' + atd.TableName + ' WHERE ' + atd.DeleteByField + ' = ' + atd.RecordId + ';'
FROM AutomationTestingData AS atd 

OPEN deleting_cursor  

FETCH NEXT FROM deleting_cursor   
INTO @query  

WHILE @@FETCH_STATUS = 0  
BEGIN  

    EXEC(@Query)

    FETCH NEXT FROM deleting_cursor   
    INTO @query  
END   
CLOSE deleting_cursor;  
DEALLOCATE deleting_cursor;  

CURSOR SQL : https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-2017

EXECUTE dynamic SQL Server : https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017

How to implement score points for each WHERE clause in SELECT statement

Simple way to solve your problem is:

SELECT *,
        (
         CASE WHEN `property_max-price` < 550000 THEN 1 ELSE 0 END 
          + 
         CASE WHEN property_additional LIKE "%hot_water%" THEN 1 ELSE 0 END
          +
         CASE WHEN  `property_floor-from` >= 2 AND `property_floor-to` <=5 THEN 1 ELSE 0 END
        ) / 3 * 100 AS `%`
FROM client

---

Related Topics