CODEWITHSUNDEEP
mysqlselectphpmyadmin
Pepijn
Pepijn

Reputation: 1204

MySQL - select, not in issue

I have a problem, sometimes due to a human error a relation_id will appear in the database, of relations and there is no real id for this.

SELECT relation_id
FROM relations
WHERE relation_id NOT 
IN (
    SELECT id
    FROM relations
)

This will return the fields where this has happened, I would like to switch the values of relation_id to NULL.

Here is what I've got so far:

UPDATE `relations` SET relation_id = NULL WHERE relation_id NOT IN (SELECT id FROM relations)

This off course works for any other table, only not for relations. Does anybody know a work-around? I'm sure there is a way

Upvotes: 0

Views: 105

Answers (3)

spencer7593
spencer7593

Reputation: 108490

This is what I would try:

UPDATE relations r
  LEFT JOIN relations s 
    ON r.relation_id = s.id
   SET r.relation_id = NULL 
 WHERE s.id IS NULL
   AND r.relation_id IS NOT NULL;

(I'm not sure if that will work; that may raise an exception because the relations table is referenced twice.)

If the multi-table update doesn't work, I would create a work table, populate the work table with a query (that identifies the rows to be updated) and then run a multi-table update using the work table.

This query will identify rows that have a relation_id that doesn't point to an existing id:

SELECT r.*
  FROM relations r
  LEFT JOIN relations s 
    ON r.relation_id = s.id
 WHERE s.id IS NULL
  AND r.relation_id IS NOT NULL;

You don't need to pull all the columns, only the primary key. (I'm assuming here that the primary key is the single column id.)

CREATE TABLE work_table (id int PRIMARY KEY);

INSERT INTO work_table (id)
SELECT r.id
  FROM relations r
  LEFT JOIN relations s 
    ON r.relation_id = s.id
 WHERE s.id IS NULL
   AND r.relation_id IS NOT NULL;

UPDATE relations r
  JOIN work_table s
    ON r.id = s.id
   SET r.relation_id = NULL;

DROP TABLE work_table;

Addendum:

If you are using InnoDB, you can avoid this type of data integrity problem in the future by defining a foreign key constraint. For example:

ALTER TABLE relations ADD CONSTRAINT relations_FK
FOREIGN KEY (relation_id) REFERENCES relations(id)
ON UPDATE CASCADE ON DELETE SET NULL;

This will prohibit changes to the table which would result in a relation_id having a value other than a value of id that exists in the table. (This requires that id is the PRIMARY KEY, and that relation_id has an identical datatype.)

Upvotes: 1

raina77ow
raina77ow

Reputation: 106453

Well, the reason for error is actually well-described in the error message itself... You cannot specify the table you update in the inner SELECT. But who said that we cannot go deeper?

UPDATE relations 
   SET relation_id = NULL 
 WHERE relation_id NOT IN (
       SELECT id FROM (SELECT id FROM relations) AS take_that_sql
);

Here is an SQLFiddle to play with. )

Upvotes: 3

Sashi Kant
Sashi Kant

Reputation: 13465

Try this ::

SELECT r1.relation_id
FROM relations r1
left join relations r2 on (r1.relation_id=r2.id)
where r2.id is null

Upvotes: 0

Related Questions