Jump to content

Make 2 update queries 1


Go to solution Solved by Barand,

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?

Link to comment
https://forums.phpfreaks.com/topic/300023-make-2-update-queries-1/
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.

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'

  • Solution

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.