MySQL DELETE QUERY ERROR

Question

Please help to correct the following mysql query,

It is throwing error like

'operand should contain one column'

delete from l0_clarabridge_s.incr_sentence_class_xref where   incr_sentence_class_xref.document_id NOT IN (
select *
from l0_clarabridge_s.incr_sentence_class_xref
join l0_clarabridge_s.incr_document
on incr_sentence_class_xref.document_id = incr_document.document_id);

Possible duplicate of [Operand Should Contain 1 Column - MySQL NOT IN](http://stackoverflow.com/questions/6318208/operand-should-contain-1-column-mysql-not-in) — Ullas, Jun 13 '16 at 08:17
Even after adding document_id, select * from l0_clarabridge_s.incr_sentence_class_xref where document_id NOT IN ( select x.document_id from l0_clarabridge_s.incr_sentence_class_xref x join l0_clarabridge_s.incr_document d on x.document_id = d.document_id); — Lavanya D., Jun 13 '16 at 08:28
It is throwing error like 'Error Code: 1093. You can't specify target table 'incr_sentence_class_xref' for update in FROM clause' — Lavanya D., Jun 13 '16 at 08:29
Please **edit** and update your question rather than posting in comments, — Martin, Jun 13 '16 at 09:57

score 0 · Accepted Answer · answered Jun 13 '16 at 08:14

Add the document_id to your select:

delete from l0_clarabridge_s.incr_sentence_class_xref where   incr_sentence_class_xref.document_id NOT IN (
select incr_sentence_class_xref.document_id
from l0_clarabridge_s.incr_sentence_class_xref
join l0_clarabridge_s.incr_document
on incr_sentence_class_xref.document_id = incr_document.document_id);

score 0 · Answer 2 · edited Jun 13 '16 at 09:44

0

You are comparing your WHERE statement to two columns instead of one, try the following:

DELETE FROM l0_clarabridge_s.incr_sentence_class_xref WHERE   incr_sentence_class_xref.document_id NOT IN (
SELECT [insert your ID column name here]
FROM l0_clarabridge_s.incr_sentence_class_xref
JOIN l0_clarabridge_s.incr_document
ON incr_sentence_class_xref.document_id = incr_document.document_id);

edited Jun 13 '16 at 09:44

Baklap4

3,914
2
29
56

answered Jun 13 '16 at 08:14

Tom Nijs

3,835
3
22
40

MySQL DELETE QUERY ERROR

2 Answers2