UPDATE table from another table, SELECT is good but UPDATE is wrong

Question

I have to match two tables based on the rows clients.client (VARCHAR) and clients_info.client (VARCHAR) (like a text key).

clients_info has a one to many relationship to clients because the clients have various spellings that can match. There is no id field in the clients_info table and I'd like to populate that with the existing clients.idx.

When I run this query I get the results I want with a key I can associate with many groups of these spellings.

SELECT 
  clients_info.client, 
  clients.client AS c_client, 
  clients_info.idx 
FROM clients
INNER JOIN clients_info ON (clients_info.client = clients.client);

Output:

client           | c_client        |  idx
------------------------------------------
Real Corp        | Real Corp        | 180
Real Corp        | Real Corp        | 180
Real Corp        | Real Corp        | 180
Advantage People | Advantage People |  29
Advantage People | Advantage People |  29
Advantage People | Advantage People |  29
Others           | Others           |  392
Others           | Others           |  392

That's how the clients match up to the id they need. I've tried these but I'm not getting it right:

UPDATE clients 
INNER JOIN clients_info ON (clients.client = clients_info.client)
SET clients.client_info_id = cllients.idx;

UPDATE clients SET clients.client_info_id = (
    SELECT idx
    FROM clients_info
    WHERE clients_info.client = clients.client
    );

Answer 1

There is no id field in the clients_info table and I'd like to populate that with the existing clients.idx

The in both queries you are trying to update the clients table not the clients_info .

Below query will set the id on clients_info table equal to the idx on the client table for the matching rows.

UPDATE clients_info ci 
INNER JOIN clients c ON c.client = ci.client
SET ci.id = c.idx;