如何用WHERE子句中的SELECT语句编写SQL DELETE语句？

Question

数据库：Sybase Advantage 11

在我对数据进行规范化的过程中，我试图删除从以下select语句中得到的结果：

SELECT tableA.entitynum
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 FROM tableA
WHERE (SELECT q.entitynum
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'))
;

ERROR IN SCRIPT: poQuery: Error 7200:  AQE Error:  State = S0000;   NativeError = 2124;
[iAnywhere Solutions][Advantage SQL Engine]Invalid operand for operator: = Boolean value
cannot be operated with non-Boolean value.

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

ERROR IN SCRIPT: poQuery: Error 7200:  AQE Error:  State = 42000;   NativeError = 2117;
[iAnywhere Solutions][Advantage SQL Engine] Unexpected token: INNER -- Expecting semicolon.
-- Location of error in the SQL statement is: 23 (line: 2 column: 1)

Answer 1

您需要标识TableA中的主键，以便删除正确的记录。主键可以是唯一标识表中一行的单个列或几个列的组合。如果没有主键，则可以使用ROWID伪列作为主键。

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