如何在 WHERE 子句中使用 SELECT 语句编写 SQL DELETE 语句? [英] How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?

问题描述

数据库:Sybase Advantage 11

Database: Sybase Advantage 11

为了规范化数据，我试图删除从这个 SELECT 语句中得到的结果:

On my quest to normalize data, I am trying to delete the results I get from this SELECT statement:

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 语句:

This is the DELETE statement I have come up with:

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

当我尝试运行此语句时，我不断收到此错误:

I continuously get this error when I try to run this statement:

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)

有人可以帮助我正确构建一个 DELETE 查询，以便删除正确的数据吗?

Could someone aid me in properly constructing a DELETE query that will result in the proper data being removed?

推荐答案

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

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

这篇关于如何在 WHERE 子句中使用 SELECT 语句编写 SQL DELETE 语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！