Updating a column in a MySQL table based on some comparisons in another table-CodePudding

I'm new to MySQL and need some help to achieve this result. My question goes as follows:

I have two tables A and B where table B is subset of table A.

Then, I add another column (say flag1 having default value 0) in table A.

I want to set value flag1=1 in table A where all the rows from table B are matching.

How can I achieve this in MySQL? Any help would be appreciated

CodePudding user response：

    UPDATE TableA AS a
RIGHT JOIN TableB AS b
        ON a.col1 = b.col1
           AND a.col2 = b.col2
           AND a.col3 = b.col3
           AND a.col4 = b.col4
           AND a.col5 = b.col5
           AND a.col6 = b.col6
       SET a.flag1 = 1

The Right Join restricts the rows of TableA to the ones present in TableB and being identical on all six columns.

sqlfiddle