如何在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
语句:
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屋!