cobusbo Posted December 27, 2015 Share Posted December 27, 2015 Hi I have 2 tables I need to update according to the value of the 3rd table field currently I do 2 queries but I'm sure there must be a way to make it 1 query query 1 $sql = "UPDATE Room_users INNER JOIN Users2 u ON u.mxitid = Room_users.mxitid SET Room_users.User = u.Username"; query 2 $sql = "UPDATE Rooms INNER JOIN Users2 u ON u.mxitid = Rooms.mxitid SET Rooms.creator = u.Username"; How can I turn this into 1 query? Quote Link to comment Share on other sites More sharing options...
Barand Posted December 27, 2015 Share Posted December 27, 2015 Not tested UPDATE Room_users INNER JOIN Users2 u ON u.mxitid = Room_users.mxitid INNER JOIN Rooms u ON u.mxitid = Rooms.mxitid SET Room_users.User = u.Username ,Rooms.creator = u.Username Duplicating the username across three tables is destroying your database normalization. You should store it in one place only (Users) and retrieve it with a join when needed. Quote Link to comment Share on other sites More sharing options...
cobusbo Posted December 27, 2015 Author Share Posted December 27, 2015 Not tested UPDATE Room_users INNER JOIN Users2 u ON u.mxitid = Room_users.mxitid INNER JOIN Rooms u ON u.mxitid = Rooms.mxitid SET Room_users.User = u.Username ,Rooms.creator = u.Username Duplicating the username across three tables is destroying your database normalization. You should store it in one place only (Users) and retrieve it with a join when needed. getting the following error Connection failed: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'u' Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted December 27, 2015 Solution Share Posted December 27, 2015 Sorry, UPDATE Room_usersINNER JOIN Users2 u ON u.mxitid = Room_users.mxitidINNER JOIN Rooms u ON u.mxitid = Rooms.mxitid -- remove the "u" alias on this lineSET Room_users.User = u.Username ,Rooms.creator = u.Username Quote Link to comment Share on other sites More sharing options...
cobusbo Posted December 27, 2015 Author Share Posted December 27, 2015 Thank you Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.