CODEWITHSUNDEEP
mysqlsqlselectjoinsql-update
meallhour
meallhour

Reputation: 15599

MYSQL: Getting an error while updating table

I have written below query to update mysql table

update table1
set val_col = 'TRUE'
where id IN(
SELECT ID  
FROM table1 a INNER JOIN
     table2 b
     ON a.a_id = b.a_id
WHERE a.create_dt >= '2017-01-07' AND
      b.check_status = 'FAIL' AND
      b.check_type = 'check1' AND
      b.timestamp_val = (SELECT MAX(b2.timestamp_val)
                         FROM table2 b2
                         WHERE b2.a_id = b.a_id AND
                               b2.check_status = b.check_status AND
                               b2.check_type = b.check_type
                        ));

I am getting an error

You can't specify target table 'table1' for update in FROM clause

Upvotes: 0

Views: 118

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270663

Just use a JOIN:

UPDATE table1 t1 JOIN
       (SELECT ID  
        FROM table1 a INNER JOIN
             table2 b
             ON a.a_id = b.a_id
        WHERE a.create_dt >= '2017-01-07' AND
              b.check_status = 'FAIL' AND
              b.check_type = 'check1' AND
              b.timestamp_val = (SELECT MAX(b2.timestamp_val)
                                 FROM table2 b2
                                 WHERE b2.a_id = b.a_id AND
                                       b2.check_status = b.check_status AND
                                       b2.check_type = b.check_type
                                )
       ) tt
       ON t1.id = tt.id
    set t1.val_col = 'TRUE';

I suspect that you can also simplify the logic.

Upvotes: 0

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

The error is pretty clear that tells, you are trying to update table1 but table1 also used in the where clause. So, creating an inner select and aliasing it should do the trick;

update table1
set val_col = 'TRUE'
where id IN(
select * from (
SELECT ID  
FROM table1 a INNER JOIN
     table2 b
     ON a.a_id = b.a_id
WHERE a.create_dt >= '2017-01-07' AND
      b.check_status = 'FAIL' AND
      b.check_type = 'check1' AND
      b.timestamp_val = (SELECT MAX(b2.timestamp_val)
                         FROM table2 b2
                         WHERE b2.a_id = b.a_id AND
                               b2.check_status = b.check_status AND
                               b2.check_type = b.check_type
                        )) aliasTable1);

Upvotes: 1

Related Questions