MySQL How to update one field value in a table equals a field value for another table

updatea inner join b on a.bid=b.id seta.x=b.x,a.y=b.y ;

This is a small trick to implement a batch update.
As an example:

1234567

UPDATEcategories    SETdisplay_order = CASEid        WHEN1 THEN3        WHEN2 THEN4        WHEN3 THEN5    ENDWHEREid IN(1,2,3)

This SQL means, update the Display_order field, if id=1 the value of Display_order is 3, if id=2 Display_order value is 4, if id=3 then Display_order value is 5.
That is, the conditional statements are written together.
The where section here does not affect the execution of the code, but it improves the efficiency of SQL execution. Make sure that the SQL statement executes only the number of rows that need to be modified, where only 3 of the data is updated, while the WHERE clause ensures that only 3 rows of data are executed.

If you update multiple values, you only need to modify them slightly:

010203040506070809101112

UPDATEcategories    SET display_order = CASEid        WHEN1 THEN3        WHEN2 THEN4        WHEN 3 THEN5    END,    title = CASEid        WHEN1 THEN‘New Title 1‘        WHEN2 THEN‘New Title 2‘        WHEN3 THEN‘New Title 3‘    ENDWHEREid IN(1,2,3)

Here, a MySQL statement has been completed to update multiple records.
But to use in the business, you need to combine the service-side language, here in PHP, for example, constructs this MySQL statement:

0102030405060708091011121314151617

$display_order= array(    1 => 4,    2 => 1,    3 => 2,    4 => 3,    5 => 9,    6 => 5,    7 => 8,    8 => 9);$ids= implode(‘,‘, array_keys($display_order));$sql = "UPDATE categories SET display_order = CASE id ";foreach($display_orderas$id=> $ordinal) {    $sql.= sprintf("WHEN %d THEN %d ", $id, $ordinal);}$sql.= "END WHERE id IN ($ids)";echo$sql;

MySQL How to update one field value in a table equals a field value for another table