Jump to content
cobusbo

Make 2 update queries 1

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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'

Share this post


Link to post
Share on other sites

Sorry,

 

UPDATE
    Room_users
INNER JOIN
    Users2 u ON u.mxitid = Room_users.mxitid
INNER JOIN

    Rooms  ON u.mxitid = Rooms.mxitid                                         -- remove the "u" alias on this line
SET

    Room_users.User = u.Username
   ,Rooms.creator = u.Username

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.