How to update a column with information from another table-CodePudding

I have a table defined like this:

CREATE TABLE employees 
    (employee_id    INT(6),
     email          VARCHAR(25) NOT NULL);

and another one defined like this:

CREATE TABLE personne (
    nopers          NUMERIC(6)  PRIMARY KEY,
    courriel        VARCHAR(40));

I'd like to fill the column "courriel" from table "personne" with the corresponding "email" from table "employees", based on corresponding "employee_id = nopers", and add "@technissimo.fr" to all emails.

I tried this, but it doesn't seem to work:

UPDATE personne
   SET courriel = CONCAT(employees.email, '@technissimo.fr')
 WHERE employees.email =
       (SELECT email FROM personne JOIN employees ON employee_id = nopers);

Can someone provide me with elements of answer on this one?

CodePudding user response：

The column email followed by the WHERE and SET clauses should belong to the personne table, those issues yield errors.

The query might be rearranged in the syntax like the below one with no need of subquery while still keeping the existing matching condition ON employee_id = nopers :

UPDATE personne 
  JOIN employees
    ON employee_id = nopers
   SET courriel = CONCAT(email,'@technissimo.fr')

Demo