CODEWITHSUNDEEP
mysqlsqldatabase
Arham Ali Qureshi
Arham Ali Qureshi

Reputation: 159

Update my database from Select result

mysql> SELECT Ext, Pass, Name, Context FROM temp_Users WHERE temp_Users.Pass NOT IN (SELECT Pass FROM Users);
+------+-------+---------+------------+
| Ext  | Pass  | Name    | Context    |
+------+-------+---------+------------+
| 6003 | Hello | WebPone | DLPN_Admin |
+------+-------+---------+------------+
1 row in set (0.00 sec)


mysql> UPDATE Users
    -> SET (Pass, Name, Context) = (SELECT  Pass, Name, Context FROM temp_Users WHERE temp_Users.Pass NOT IN (SELECT Pass FROM Users))
    -> WHERE Users.Ext = temp.Ext;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(Pass, Name, Context) = (SELECT  Pass, Name, Context FROM temp_Users WHERE temp_' at line 2

I want to update my database from Select result and i am getting this error. Please tell me how i can resolve it ?

Upvotes: 0

Views: 695

Answers (2)

ruakh
ruakh

Reputation: 183381

MySQL doesn't support the SET ( multiple_fields ) = ( subquery_that_returns_multiple_fields ) syntax for UPDATE statements. Instead, you have to use a "multiple-table" update (a join). See http://dev.mysql.com/doc/refman/5.6/en/update.html.

Your query has some other problems as well, so I'm not clear on exactly what you want . . . but I think you want something like this:

UPDATE users
  JOIN temp_users
    ON temp_users.ext = users.ext
   SET users.pass = temp_users.pass,
       users.name = temp_users.name,
       users.context = temp_users.context
 WHERE temp_users.pass NOT IN
        -- extra subquery to bypass MySQL limitation:
        ( SELECT pass
            FROM ( SELECT pass
                     FROM users
                 ) t
        )
;

Upvotes: 3

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

UPDATE Users u
JOIN temp_Users tu ON tu.Ext = u.Ext
SET 
    Pass = tu.Pass,
    Name = tu.Name,
    Context = tu.Context
WHERE tu.Pass NOT IN (
    SELECT Pass
    FROM Users
)

Upvotes: 2

Related Questions