MySQL select results to perform update example tutorial

1. Single table query -> update

UPDATE table_name
SET field1=new-value1, field2=new-value2
[WHERE Clause]

2. Multi-table joint query -> update

UPDATE a
INNER JOIN (SELECT yy FROM b) c ON a.id = c.id 
SET a.xx = c.yy
[WHERE Clause]

• The above INNER JOIN can be replaced by LEFT JOIN, RIGHT JOIN and other joint queries.
• The field after SET must be a field in table a. The field can be equal to a constant or a column. If it is not a field in table a, the message "The target table b of the UPDATE is not updatable" will be reported.
• The WHERE clause must be placed after SET
• The a table after UPDATE is not the result of a query, nor can it be a SELECT clause.
  

example:

tableex_copy1 table

tablein_copy1 table

Query SQL

SELECT * FROM
tableex_copy1 a LEFT JOIN
(SELECT * FROM tablein_copy1) b
ON a.BID = b.AID
WHERE b.ASEX = 'female'

update SQL

UPDATE
#SELECT * FROM
tableex_copy1 a LEFT JOIN
(SELECT * FROM tablein_copy1) b
ON a.BID = b.AID
SET a.CESHI = '6666'
WHERE b.ASEX = 'female'

Tableex_copy1 table after update

Summarize

This is the end of this article about MySQL executing update on select results. For more relevant content about MySQL executing update on select results, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!