How to update fields with fields from another table?

Question

How to update fields with fields from another table?

Navigation

#1 by (6 votes)

5

I'm not able to update a table with the select of another.

I have a mdl_user table that 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 stay updated, but I only managed to 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

How do I update the username , firstname , and lastname fields of all users at once?

mysql sql sql-update query

asked by anonymous 17.08.2016 / 16:03

1 answer

Serialize Object to JSON filter records per month

score 6 · Accepted Answer

You can do a UPDATE WITH JOIN with the two tables to update the 3 columns, user o 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 have 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