CODEWITHSUNDEEP
mysqlsql-delete
Vendula Novosadova
Vendula Novosadova

Reputation: 11

Deleting rows from table specified by selection from same and other table (inner join)

I need to delete some rows from my DB, which I've selected with a selection sql statement

I've tried using aliases, but they also did not work

DELETE FROM OBSERVATION WHERE OBSERVATION.ID in
(SELECT OBSERVATION.ID from OBSERVATION
inner join SPECIMEN on SPECIMEN.ID=OBSERVATION.SPECIMEN_ID
WHERE SPECIMEN.GENDER="male"and OBSERVATION.VALUE_TERM is not null and OBSERVATION.PARAMETER_STABLE_ID="IMPC_PAT_028_002")

I always get the error:

Error in query (1093): You can't specify target table 'OBSERVATION' for update in FROM clause

Upvotes: 0

Views: 32

Answers (4)

DataVader
DataVader

Reputation: 780

DELETE targetTable
FROM targetTable
INNER JOIN otherTable
    ON targetTable.col = otherTable.col
WHERE otherTable.col2 = “foo”

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

On the assumption that observation.id is a PRIMARY KEY, why not use:

DELETE o
  FROM observation o
  JOIN specimen s
    ON s.id = o.specimen_id
 WHERE s.gender = 'male' 
   AND o.value_term IS NOT NULL 
   AND o.parameter_stable_id = 'IMPC_PAT_028_002'

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

Mysql don't allow a delete form the same table used for select the rows to delete for avoid this you must buil a set of rows not related to the original table

You could use a inner join on a subquery instead of in clause
this should be enough 

DELETE 
FROM OBSERVATION 
INNER JOIN  (
    SELECT OBSERVATION.ID 
    from OBSERVATION
    inner join SPECIMEN on SPECIMEN.ID=OBSERVATION.SPECIMEN_ID
    WHERE SPECIMEN.GENDER="male"
    and OBSERVATION.VALUE_TERM is not null
     and OBSERVATION.PARAMETER_STABLE_ID="IMPC_PAT_028_002"
)  t  OBSERVATION.ID = t.ID

otherwise you need to enforce this way

DELETE 
FROM OBSERVATION 
INNER JOIN  (
  select id from( 
    SELECT OBSERVATION.ID 
    from OBSERVATION
    inner join SPECIMEN on SPECIMEN.ID=OBSERVATION.SPECIMEN_ID
    WHERE SPECIMEN.GENDER="male"
    and OBSERVATION.VALUE_TERM is not null
     and OBSERVATION.PARAMETER_STABLE_ID="IMPC_PAT_028_002"

     ) t1
)  t  OBSERVATION.ID = t.ID

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

Since you are using OBSERVATION table in the DELETE and sub query, so you can use table alias name and mention it properly on the DELETE statement.

The following query will work in your scenario:

DELETE OB
FROM OBSERVATION OB
WHERE OB.ID IN (
    SELECT SOB.ID 
    FROM OBSERVATION SOB
    INNER JOIN SPECIMEN SP ON SP.ID = SOB.SPECIMEN_ID
    WHERE SP.GENDER = "male" AND 
          SOB.VALUE_TERM IS NOT NULL AND 
          SOB.PARAMETER_STABLE_ID = "IMPC_PAT_028_002"
)

Upvotes: 0

Related Questions