MySQL Query: From SELECT to UPDATE

Vote count:

0

I've built out this SELECT query and it returns the correct results:

SELECT CONCAT_WS(' ', n.contact_first_name, n.contact_last_name), n.contact_id, s.contact_id
  FROM contact n
  JOIN demographics s ON s.contact_id = n.contact_id

I get a table with the two names concatenated and the matching contact_id. My purpose with this is to write all the contact_first_name and contact_last_name into the demographics table under the column name demographics_name. However, I've tried to 'convert' my SELECT statement into an UPDATE and I ran into a duplicate column name issue. Here's the query I've tried to build:

UPDATE demographics d
    INNER JOIN (SELECT n.contact_first_name, n.contact_last_name, n.contact_id, s.contact_id
          FROM contact n
          JOIN demographics s ON s.contact_id = n.contact_id
    ) c 
    ON c.contact_id = d.contact_id
    SET d.demographics_name = CONCAT_WS(' ', c.contact_first_name, c.contact_last_name)

I can't spot the mistake I've made and I could really use the help of a fresh set of critical eyes to point out what I'm missing here.

asked 20 secs ago

Jonny Sooter

1,646

MySQL Query: From SELECT to UPDATE