CODEWITHSUNDEEP
mysqlsqlsql-update
Bubba
Bubba

Reputation: 180

MySql Error 1093 trying to UPDATE using two databases and two tables

DataBase_1 has a list of users with their vBulletinId

DataBase_2 has the vBulletin users with their vBulletinId and UserGroupId

I want to find all vBulletin users from Database_2, who are NOT in Database_1, and change their UserGroupId to 15

UPDATE database_2.user 
SET 
    usergroupid = 15
WHERE
(
    SELECT UserId
    FROM database_2.user 
    WHERE UserId NOT IN 
    (
            SELECT vBulletinId 
            FROM database_1.Users 
    )
);

Currently I am getting a "You can't specify target table 'user' for update in FROM clause"

Any help would be great. thanks.

=== SOLUTION ===

A simplified solution that worked.

UPDATE database_2.user 
SET 
    usergroupid = 15
WHERE userid NOT IN 
(
    SELECT vBulletinId FROM database_1.Users
);

Upvotes: 1

Views: 61

Answers (1)

Akina
Akina

Reputation: 42661

UPDATE database_2.user
LEFT JOIN database_1.Users ON database_2.user.UserId = database_1.Users.vBulletinId 
SET database_2.user.usergroupid = 15
WHERE database_1.Users.vBulletinId IS NULL

or

UPDATE database_2.user
SET database_2.user.usergroupid = 15
WHERE NOT EXISTS ( SELECT NULL
                   FROM database_1.Users
                   WHERE database_2.user.UserId = database_1.Users.vBulletinId )

Upvotes: 1

Related Questions