How to insert value in a column of a table based on a selection of another table

Question

I need help to correct the following code:

UPDATE table_2 SET table_2.col_2 = '1'
FROM table_2 
INNER JOIN table_1 ON table_2.col_1 = table_1.col_1 
AND table_1.col_2 IN (2,6,11,16,19,22);

There are 2 tables that have the same first column (that is, col_1 is the same for table_1 and table_2).

The second column of table_1 is filled with numbers, while the second column of table_2 is empty.

I need to check the rows of col_2 of table_1 that have the same values as 2, 6, 11, 16, 19 or 22. Then, I need to insert the value "1" in the respective cell of col_2 of table_2 that shares the same value for col_1.

The below picture makes it easier to understand:

Thanks.

Answer 1

Here is the MySQL UPDATE .. JOIN ... SET syntax:

UPDATE table_2 t2
INNER JOIN table_1 t1 
    ON t2.col_1 = t1.col_1 
    AND t1.col_2 IN (2, 6, 11, 16, 19, 22)
SET t2.col_2 = 1

NB: I assume that table_2.col_2 is of numeric datatype, so I removed the single quotes around '1'.