“This is the 10th day of my participation in the First Challenge 2022. For details: First Challenge 2022”

Subject to a

Get the age of the user whose name is Zhang SAN and assign age to the user whose name is Zhao Liu.

Original data:

The general way to write this is:

UPDATE user 
SET age = ( SELECT age FROM user WHERE name = 'Joe' ) 
WHERE
    name = 'Daisy';
Copy the code

This is true for both Oracle and SQLServer. Mysql > select * from ‘mysql’;

When mysql encounters a subquery in the FROM clause, it first executes the subquery and puts the result into a temporary table, which is usually called a “derived table”. Temporary tables have no indexes and cannot be locked.

When an update occurs, the table is locked and cannot be selected. So I get an error,

If the result set is placed in a temporary table, the temporary table will not be locked, so it can be queried and updated normally.

Correct way to write it:

Methods a

UPDATE user 
SET age = ( SELECT age  FROM ( SELECT * FROM user ) AS tb_temp WHERE name = 'Joe' ) 
WHERE
    name = 'Daisy'
Copy the code

Check the data to prove that Zhao Liu’s age has been modified to 12 years old.

Note: tb_temp is a temporary table that stores query result sets.

Method 2

  1. Create an intermediate table:
create table   tmp as select name  newname,age newage from `user`;
Copy the code

2. Perform the following operations:

UPDATE user 
SET age = ( SELECT age FROM tmp WHERE name = 'Joe' ) 
WHERE
	name = 'Daisy';
Copy the code

The data of the original table zhao Liu has been modified to 12 years oldDrop intermediate table;

drop table tmp;
Copy the code

Methods three

Mysql > create a table TEMPORARY

First, temporary tables are only visible in the current connection, and when a connection is closed, Mysql automatically drops the table and frees all space. Therefore, you can create temporary tables with the same name in different joins and manipulate temporary tables belonging to this join. The syntax for creating a TEMPORARY table is similar to that for creating a table except that you add the keyword TEMPORARY, for example:

              CREATE TEMPORARY TABLEThe name of the table (... .).Copy the code
  1. Create temporary table:
create TEMPORARY table  tmp as select name newname , age newage  from user;
Copy the code
  1. Temporary tables are only valid for the current connection, so to check whether the table was created successfully, use the following command:
select *  from tmp;
Copy the code

  1. Update operation
UPDATE user 
SET age = ( SELECT age FROM tmp WHERE name = 'Joe' ) 
WHERE
	name = 'Daisy';
Copy the code

The data of the original table zhao Liu has been modified to 12 years old4. After the update operation is complete, close the database connection and open the database connection again to verify that the temporary table has been deleted.

select *  from tmp;
Copy the code

The following contents are not related to the above problems and are only my notes

Topic 2

Update data from one field of the same data in a table to another field of the same data

Methods a

  1. Create an intermediate table:
create table   tmp as select name  newname,age newage from `user`;
Copy the code

2. Perform the following operations:

UPDATE user s,tmp t SET s.age=t.newname WHERE s.name=t.newname;
Copy the code

Drop intermediate table;

drop table tmp;
Copy the code

Method 2

Mysql > create a table TEMPORARY

First, temporary tables are only visible in the current connection, and when a connection is closed, Mysql automatically drops the table and frees all space. Therefore, you can create temporary tables with the same name in different joins and manipulate temporary tables belonging to this join. The syntax for creating a TEMPORARY table is similar to that for creating a table except that you add the keyword TEMPORARY, for example:

              CREATE TEMPORARY TABLEThe name of the table (... .).Copy the code
  1. Create temporary table:
create TEMPORARY table  tmp as select name newname , age newage  from user;
Copy the code
  1. Temporary tables are only valid for the current connection, so to check whether the table was created successfully, use the following command:
select *  from tmp;
Copy the code

  1. Update operation
UPDATE user s,tmp t SET s.age=t.newname WHERE s.name=t.newname;
Copy the code
  1. After the update operation is complete, close the database connection and reopen the database connection to verify that the temporary table has been deleted.
select *  from tmp;
Copy the code