Update all the columns that have the same name between two tables in MySQL

There is no SQL clause that allows you to do what you expect, the only way is to dynamically build an update query as mentioned by Javier before. Let's see what it would be like:

First of all we have two tables that have some fields with the same name (we assume they are the same type and the same length)

create table tabla1 (
    id int,
    col1 int,
    col2 int,
    col3 int,
    col4 int
);

create table tabla2 (
    id int,
    col1 int,
    col2 int,
    col3 int
);

INSERT INTO tabla1 (id, col1, col2, col3, col4) values (1, 0, 0, 0, 0);
INSERT INTO tabla2 (id, col1, col2, col3) values (1, 1, 1, 1);

And we want to update the data of Tabla1 of the "shared" fields without counting the id with the same values of the tabla2 . In our example, the fields col1 , col2 and col3 and we discard the id because we obviously do not want to update it. For this we are going to build a statement of update dynamic% from the table INFORMATION_SCHEMA.COLUMNS

SET @campos = '';

SELECT  GROUP_CONCAT( concat('    a.',C1.COLUMN_NAME, ' = b.', C1.COLUMN_NAME) separator ',\n' )
  INTO @campos
  FROM INFORMATION_SCHEMA.COLUMNS C1
  INNER JOIN INFORMATION_SCHEMA.COLUMNS C2
      ON C2.COLUMN_NAME = C1.COLUMN_NAME
  WHERE C1.TABLE_NAME = 'tabla1'
        AND C2.TABLE_NAME = 'tabla2'
        AND C1.COLUMN_NAME <> 'id';
        AND C1.TABLE_SCHEMA = DATABASE()
        AND C2.TABLE_SCHEMA = DATABASE();

-- Seteamos el id que queremos actualizar  
SET @id  = '1';

SET @sql = CONCAT('UPDATE tabla1 a\nJOIN tabla2 b\n    ON a.id = b.id\nSET\n', @campos, '\nWHERE a.id = ', @id, '');

SELECT @sql;

With this we complete our variable @Sql with the following information:

UPDATE tabla1 a
JOIN tabla2 b
    ON a.id = b.id
SET
    a.col1 = b.col1,
    a.col2 = b.col2,
    a.col3 = b.col3
WHERE a.id = 1

Now it is clear what we want to achieve, we would simply run this query in the following way:

-- Actualización
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

As far as I know, there's no way to save yourself from specifying the names of the columns. If you are using an interpreted language that allows you to manipulate the SQL before launching it, you could build the list of columns by questioning the schema of the database to get the list of columns.

The following query returns the list of columns in a table that start with "col":

SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tabla1'
  AND column_name LIKE 'col%';

From that list the query could be elaborated. If you are launching the query directly, then you could first get the list of columns you want and then take them to your favorite editor to prepare the query. Any editor that allows macros will save you a lot of time.

If you use a user who can see several databases, in the schema query you may have to specify the name of the database using AND table_schema = 'nombre_de_la_db' .