CODEWITHSUNDEEP
mysqlsqldatabaseinnodb
Wasiq Muhammad
Wasiq Muhammad

Reputation: 3118

Not Allowing SQL Update Query

I want to select some id from a table based on a condition, and update them. 

 UPDATE user_feed
    SET `status` = 'DELETED'
    WHERE
        id IN (
            SELECT
                feed.id
            FROM
                user_activity act
            JOIN user_feed feed ON act.id = feed.activity_id
            WHERE
                act.uid = 16
            AND feed. STATUS = 'ACTIVE'
        )

Showing this error

[SQL]UPDATE user_feed set status='DELETED' Where id IN (select feed.id from user_activity act join user_feed feed on act.id = feed.activity_id where act.uid = 16 and feed.status = 'ACTIVE')

[Err] 1093 - You can't specify target table 'user_feed' for update in FROM clause

Upvotes: 1

Views: 153

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

In MySQL, you can use join in the update statement. I think this is what you want:

UPDATE user_feed f join
       user_activity a
       on a.id = f.activity_id and a.uid = 16 
    set f.`status` = 'DELETED'
    Where f.status = 'ACTIVE';

Upvotes: 1

Related Questions