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

by

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"));

More Related Contents:

  1. Whats wrong in this sql query? It throws error [closed]
  2. SQL JOIN – WHERE clause vs. ON clause
  3. SQL update from one Table to another based on a ID match
  4. SQL – HAVING vs. WHERE
  5. How can I delete using INNER JOIN with SQL Server?
  6. Conditional WHERE clause in SQL Server
  7. Left Join With Where Clause
  8. SQL/mysql – Select distinct/UNIQUE but return all columns?
  9. SQL SELECT WHERE field contains words
  10. What is the difference between HAVING and WHERE in SQL?
  11. How to delete duplicate entries?
  12. JOIN two SELECT statement results
  13. SQL Query to get aggregated result in comma separators along with group by column in SQL Server
  14. SQL SELECT speed int vs varchar
  15. Return a value if no record is found
  16. How to get a list of column names on Sqlite3 database?
  17. How to delete from select in MySQL?
  18. SQL server ignore case in a where expression
  19. How can I select from list of values in Oracle
  20. Is a JOIN faster than a WHERE?
  21. Best way to delete millions of rows by ID
  22. How to efficiently delete rows while NOT using Truncate Table in a 500,000+ rows table
  23. SQL: how to select a single id (“row”) that meets multiple criteria from a single column
  24. MySQL: Select DISTINCT / UNIQUE, but return all columns?
  25. How to select records without duplicate on just one field in SQL?
  26. Select distinct from multiple fields using sql
  27. Oracle date “Between” Query
  28. SQL: Select rows with a column value that occurs at least N times?
  29. “SELECT *” vs “SELECT ” for performance
  30. Finding unmatched records with SQL

Leave a Comment