CODEWITHSUNDEEP
mysqlsql
Capfer
Capfer

Reputation: 901

Mysql Update With WHERE IN AND JOINS

I tried different ways, but I can't figure out where I need to place braces and how to correctly make this query work.

The error I getting is:

ERROR 1093 (HY000): You can't specify target table 'tb1' for update in FROM clause

Code:

    UPDATE tb1
SET company_id=258
WHERE id IN
    (SELECT c.well_id container_well_id
     FROM tb2 b
     INNER JOIN tb3 c ON b.id = c.id
     INNER JOIN tb1 w ON w.id = c.well_id
     WHERE b.id_number IN (2609,
                           4279,
                           4082,
                           4083,
                           4117,
                           3818,
                           3154,
                           3813,
                           3822,
                           3819)
       AND c.company_id = 258
       AND w.company_id = 259 );

Upvotes: 1

Views: 59

Answers (2)

forpas
forpas

Reputation: 164089

Just add an outer select to nest your current subquery:

UPDATE tb1
SET company_id=258
WHERE id IN
  (SELECT t.container_well_id FROM
    (SELECT c.well_id container_well_id
     FROM tb2 b
     INNER JOIN tb3 c ON b.id = c.id
     INNER JOIN tb1 w ON w.id = c.well_id
     WHERE b.id_number IN (2609,
                           4279,
                           4082,
                           4083,
                           4117,
                           3818,
                           3154,
                           3813,
                           3822,
                           3819)
       AND c.company_id = 258
       AND w.company_id = 259 ) t
  );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

One method is to use a join instead:

UPDATE tb1 JOIN
       (SELECT c.well_id container_well_id
        FROM tb2 b JOIN
             tb3 c ON b.id = c.id JOIN
             tb1 w ON w.id = c.well_id
        WHERE b.id_number IN (2609, 4279, 4082, 4083, 4117, 3818, 3154, 3813, 3822, 3819
                             ) AND
              c.company_id = 258 AND
              w.company_id = 259
        ) x
        ON x.container_well_id = tb1.id
    SET company_id = 258;

Upvotes: 1

Related Questions