phpmyadmin Question posted in
The official documentation can be found here.

How to update a different database table with info from my current database table, using mySQL? – Phpmyadmin

I’m trying to write a SQL query to update a field in a different database (Hosted on the same server with phpMyAdmin). I just want it to update the most recent row as the query will be run directly after a new user is added (on that new user). My goal output is to change the username field of this user to be the same as the email field of the user in the original database. The syntax error states

‘syntax to use near ‘FROM db2.users AS "data" WHERE db1.user.email =
…’ at line 3′

I can’t see where I’m going wrong – I saw another answer where a user said that mySQL does not support the FROM keyword, however I have not been able to find anywhere else that backs that up. This is what I currently have for my code:

UPDATE db1.user
SET username = data.username
FROM db2.users AS "data"
WHERE db1.user.email = data.email
AND db1.user.id = (
SELECT MAX(id) 
FROM db1.user
)
LIMIT 1

If anyone knows where I’m going wrong with this it would be much appreciated!

Tags:

2

Answers


  1. 0

    In MySQL, the syntax would be:

    UPDATE db1.user u JOIN
       db2.users u2
       ON u.email = u2.email JOIN
       (SELECT MAX(u2.id) as max_id
        FROM db1.user u2
       ) uu
       ON uu.max_id = u.id
    SET u.username = u2.username;

    Notes:

    It seems odd that you are not filtering by email to get the maximum id, but that is how your question is stated. Also, MySQL doesn’t support LIMIT in multi-table UPDATE queries.

    Login or Signup to reply.
  2. 0

    As in your SQL you are okay with using subqueries, I would suggest to try the following:

    UPDATE
    db1.user
SET
    username = (
    SELECT
        data.email
    FROM
        db2.users AS "data"
    WHERE
        db1.user.email = data.email)
WHERE
    db1.user.id = (
    SELECT
        MAX(id)
    FROM
        db1.user)
LIMIT 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search