数据库: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
我想出的声明:
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)
有人可以帮助我正确构造一个DELETE查询,从而导致删除正确的数据吗?
您需要在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'));
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句