How to update fields with fields from another table?

Question

I’m not able to update one table with another’s select.

I have a table mdl_user which contains several columns and created another users_sg to copy some columns.

I copied with

INSERT INTO
  users_sg (id, username, firstname, lastname)
SELECT
  id, username, firstname, lastname FROM mdl_user

I wanted to keep updating, but I could only do one column and one user at a time:

UPDATE
  users_sg
SET
  users_sg.username = (SELECT username FROM mdl_user where id=3)
WHERE id=3

I have to update the fields username, firstname and lastname of all users at once?

Answer 1

You can make a UPDATE WITH JOIN with the two tables to update the 3 columns, user or Where if it is a single record that has to be changed.

UPDATE users_sg as sg 
join mdl_user as user_ on user_.Id = sg.Id
SET sg.username = user_.username,
sg.firstname = user_.firstname,
sg.lastname = user_.lastname;
-- where user_.Id = 3 -- caso seja só de um unico registro mais com os 3 campos.

To do this you will automatically need to use an event.

DELIMITER $
CREATE EVENT AtualizaTabela 
    ON SCHEDULE EVERY 1 DAY
    STARTS NOW()
    DO BEGIN 
            UPDATE users_sg as sg 
            join mdl_user as user_ on user_.Id = sg.Id
            SET sg.username = user_.username,
            sg.firstname = user_.firstname,
            sg.lastname = user_.lastname;
    END
$ DELIMITER