Make 2 update queries 1

[cobusbo]

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?

[Barand]

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.

[cobusbo]

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'

[Barand]

Sorry,

 

UPDATE
    Room_users
INNER JOIN
    Users2 u ON u.mxitid = Room_users.mxitid
INNER JOIN
    Rooms  u  ON u.mxitid = Rooms.mxitid                                         -- remove the "u" alias on this line
SET
    Room_users.User = u.Username
   ,Rooms.creator = u.Username

[cobusbo]

Thank you