How to Install and Use MySQL Workbench

Maintaining a secure database environment is vital in today's digital age. It helps prevent breaches and ensure the confidentiality of your information. A highly effective process for enhancing MySQL connection security is by implementing an SSH tunnel for remote access. This approach establishes an encrypted tunnel between your device and the server, ensuring data remains secure. SSH Tunneling SSH tunneling, also referred to as SSH port forwarding, enables the secure transmission of data between networks. By establishing an encrypted SSH tunnel, data can be safely transferred without the risk of exposure to potential threats. It possesses several benefits: Security: Encrypts data, keeping it safe from being seen or intercepted by others. Bypassing Restrictions: Allows access to services and resources blocked by firewalls. Flexibility: Can handle all network traffic types, fitting many uses. Types of SSH Tunneling SSH tunneling is of three types: Local Port Forwarding: It lets you redirect a port from your local machine to a destination machine using a tunnel. This is the method used in our guide. For example: ssh -L 3307:localhost:3306 your_username@your_server_ip Remote Port Forwarding: It lets you redirect a port from a remote machine to your local machine. This is useful for accessing local services from a remote machine. For example: ssh -R 9090:localhost:80 your_username@your_server_ip Dynamic Port Forwarding: It lets you create a SOCKS proxy to dynamically forward traffic through an SSH tunnel. This is useful for secure web browsing or bypassing firewalls. For example: ssh -R 9090:localhost:80 your_username@your_server_ip Prerequisites Before beginning, ensure you have: SSH client (OpenSSH, or PuTTY for Windows) MySQL server info SSH into the MySQL host machine securely. Setting Up Remote Access Go through these essential steps to securely set up remote access to your MySQL server through SSH tunnel: Step 1: Facilitate Connectivity For remote access, tune it to listen on an external IP. This allows SQL access from localhost to all IPs. Here’s how to do it: Access MySQL Config File Using a text editor, access the config file. On Ubuntu, it's typically located at: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf If the file isn't in its expected place, search for it with: sudo find / -name mysqld.cnf Edit bind-address Inside the file, find bind-address line, which is set to 127.0.0.1 by default, limiting server to local connections: Change the address to allow connections from all IP addresses by setting it to 0.0.0.0. Save changes by pressing Ctrl+X, Y to confirm, and Enter to exit. Restart MySQL Restart service to apply the updated settings: sudo systemctl restart mysql Step 2: Adjust Firewall By default, 3306 is the standard port in MySQL. To permit remote access, ensure this port is opened in your firewall settings. Tailor these steps to your specific firewall service. Open Port via UFW On Ubuntu, UFW is a pre-installed firewall utility. To allow traffic on 3306: sudo ufw allow from remote_ip to any port 3306 Substitute remote_ip with actual IP. Open Port via Firewalld On Red Hat-based and Fedora systems, Firewalld is the primary firewall tool. To open port 3306 for traffic, run these commands: sudo firewall-cmd --zone=public --add-service=mysql --permanentsudo firewall-cmd --reload The first command permanently allows MySQL traffic, and the second reloads the firewall to make the changes. Step 3: Open Your SSH Client Fire up your go-to SSH client. Opt for PuTTY on Windows, or the terminal if using macOS or Linux. Using Terminal (Linux or macOS) Implement this command: ssh -L 3307:localhost:3306 your_username@your_server_ip 3307: It's the local port your computer will listen to. localhost: It's a MySQL server address used by the SSH. It's where the service runs on the machine you're connecting to. 3306: The remote port where the server listens for incoming connections. username@server_ip: Your SSH login details. When required, verify the server's fingerprint. Confirm it matches by typing "yes" and pressing Enter.  Once confirmed, enter your SSH password if asked and press Enter for tunneling. After the tunnel is up, all traffic destined to local port 3307 will be forwarded to the remote machine in a secure fashion. Using PuTTY (Windows) Windows users can use the below-given instructions to perform tunneling: Launch PuTTY. From the left menu, direct to Connection > SSH > Tunnels. Input 3307 for Source port and localhost:3306 for the Destination field. Then hit Add. Navigate back to Session menu, enter server’s IP address and start the session using the Open button. Step 4: Connect to MySQL After setting up the tunnel, seamlessly link to the server through: sudo mysql -h localhost -P 3307 -u your_mysql_user -p Step 5: Verify the Connection Log into server and check if you can run queries: Additional Safeguards for Enhanced Security To further enhance the MySQL remote access security, consider the following: Implement Robust Passwords and Authentication Ensure using strong, unique passwords for both servers accounts. Implement key-based SSH authentication for added security. Here's how to set up SSH key authentication: Generate an SSH key pair via: ssh-keygen -t rsa -b 4096 -C "[email protected]" Copy the public key to the server via: ssh-copy-id your_username@your_server_ip Regularly Update Your Software Ensure that your server, client, and all associated software are consistently updated with the latest security patches and enhancements. This practice safeguards your system against known vulnerabilities and potential threats. Supervise and Audit Access Consistently examine access logs on both your MySQL and SSH server. Watch for any unusual activities or unauthorized attempts to gain access. Set up logging for both services: Check the SSH logs via: sudo tail /var/log/auth.log Enable and check MySQL logs by adding the below-given lines in the configuration file: [mysqld]general_log = 1general_log_file = /var/log/mysql/mysql-general.log You can view the general query log via: sudo cat /var/log/mysql/mysql-general.log To continuously monitor the log file in real time, use: sudo tail -f /var/log/mysql/mysql-general.log Implement IP Whitelisting Limit access to your MySQL by applying IP whitelisting. It ensures that connections are permitted only from specified IP addresses, thereby enhancing security: sudo ufw allow from your_trusted_ip to any port 3306 Replace your_trusted_ip with the IP address you trust. Troubleshooting Issues Here are a few common problems and solutions: Unable to Connect: Check SSH configuration and firewall rules. Ensure the SSH tunnel is correctly established and the server is reachable. Port Already in Use: Change the local forwarding port from 3307 to another available port. Authentication Errors: Verify your server's credentials. Ensure that the correct user permissions are set. MySQL Server Not Listening on Correct IP: Double-check the MySQL bind-address configuration and ensure the server is listening on the correct IP. Conclusion By adhering to this guide, you'll securely connect to your MySQL database via an SSH tunnel. This method not only boosts security but also enhances remote database management efficiency.  Regularly check your SSH tunnel setup to ensure a stable, secure connection. This practice ensures your data stays protected, providing peace of mind for seamless database operations. Hostman provides pre-configured and ready-to-use cloud databases, including cloud MySQL.

SQL triggers are a vital component of many database systems, allowing automated execution of specific actions when predefined events occur. Triggers act as responsive mechanisms within a database, ensuring consistency and enabling automation of repetitive tasks. These event-driven procedures are particularly effective for handling operations triggered by changes such as  INSERT,  UPDATE, or DELETE in a table. By using triggers, database administrators and developers can enforce rules, maintain logs, or even invoke complex processes with minimal manual intervention. Let’s begin by defining an example database for a small online store to understand how triggers work in practice: -- Let’s create a databse called SHOP ; CREATE DATABASE SHOP ; USE SHOP ; -- Now we create the Products table CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Stock INT, Price DECIMAL(10, 2) ); -- Then the StockAudit table CREATE TABLE StockAudit ( AuditID INT AUTO_INCREMENT PRIMARY KEY, ProductID INT, ChangeType VARCHAR(10), QuantityChanged INT, ChangeTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); Classification of SQL Triggers SQL triggers can be classified based on their scope and timing. Row-level triggers are executed once for every row affected by a database operation, making them adequate for detailed tracking of data changes. For example, when updating inventory quantities for multiple products, a row-level trigger can record changes for each product individually. Conversely, statement-level triggers run once for an entire operation, regardless of how many rows are affected. These are useful for performing global checks or logging summary information. Triggers can also be categorized by their execution timing relative to the triggering event. Before triggers are executed prior to the event, often to validate or modify data before it is written to the database. After triggers execute after the event, making them ideal for tasks such as auditing or enforcing referential integrity. This is an example of a row-level AFTER INSERT trigger which logs new product additions: -- The DELIMITER command is used to change the statement delimiter from ; to // while defining the trigger DELIMITER // CREATE TRIGGER LogNewProduct AFTER INSERT ON Products FOR EACH ROW BEGIN INSERT INTO StockAudit (ProductID, ChangeType, QuantityChanged) VALUES (NEW.ProductID, 'ADD', NEW.Stock); END; // DELIMITER ; How Triggers Operate in a Database Triggers are defined by specifying the event they respond to, the table they act upon, and the SQL statements they execute. When a trigger’s event occurs, the database automatically invokes it, running the associated logic seamlessly. This behavior eliminates the necessity for external application code to maintain consistency. For instance, consider a scenario where we need to prevent negative stock levels in our inventory. We can achieve this with a BEFORE UPDATE trigger that validates the updated stock value: DELIMITER // -- Trigger to prevent negative stock values CREATE TRIGGER PreventNegativeStock BEFORE UPDATE ON Products FOR EACH ROW BEGIN -- Check if the new stock value is less than 0 IF NEW.Stock < 0 THEN -- Raise an error if the stock value is negative SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock cannot be negative'; END IF; END; // DELIMITER ; This guarantees that no changes violating the business rules are applied to the database. Practical Advantages of Using Triggers Triggers offer numerous advantages, such as enforcing business logic directly within the database layer. This ensures that data integrity is preserved across all applications accessing the database, reducing the need for repetitive coding. By centralizing critical logic, triggers simplify maintenance and enhance consistency. For example, a trigger can automate logging of stock adjustments, saving developers from implementing this functionality in multiple application layers. Consider this AFTER UPDATE trigger: DELIMITER // -- Trigger to log stock adjustments after an update on the Products table CREATE TRIGGER LogStockAdjustment AFTER UPDATE ON Products FOR EACH ROW BEGIN -- Insert a record into the StockAudit table with the product ID, change type, and quantity changed INSERT INTO StockAudit (ProductID, ChangeType, QuantityChanged) VALUES (OLD.ProductID, 'ADJUST', NEW.Stock - OLD.Stock); END; // DELIMITER ; This trigger automatically records every stock change, streamlining audit processes and ensuring compliance. Challenges and Considerations While triggers are powerful, they are not without challenges. Debugging triggers can be tricky since they operate at the database level and their effects may not be immediately visible. For example, a misconfigured trigger might inadvertently cause cascading changes or conflicts with other triggers, complicating issue resolution. Performance is another critical consideration. Triggers that are not well designed can slow down database operations, especially if they include resource-intensive logic or are triggered frequently. For instance, a trigger performing complex calculations on large datasets can bottleneck critical operations like order processing or stock updates. To mitigate these challenges, it is advisable to: Keep trigger logic concise and efficient. Use triggers sparingly and only for tasks best handled within the database. Test triggers extensively in controlled environments before deployment. Real-World Example: Cascading Triggers Cascading triggers can ensure data integrity across related tables. Consider a database with Orders and OrderDetails tables. When an order is deleted, it is essential to remove all associated details: DELIMITER // -- Trigger to cascade delete order details after a delete on the Orders table CREATE TRIGGER CascadeDeleteOrderDetails AFTER DELETE ON Orders FOR EACH ROW BEGIN -- Delete the corresponding records from the OrderDetails table DELETE FROM OrderDetails WHERE OrderID = OLD.OrderID; END; // DELIMITER ; This ensures that orphaned records are automatically removed, maintaining database consistency without manual intervention. However, cascading triggers require careful documentation to avoid unintended interactions. Optimizing Trigger Performance To prevent performance bottlenecks, triggers should handle minimal logic and avoid intensive operations. For tasks requiring significant processing, consider using scheduled jobs or batch processes instead. For example, instead of recalculating inventory levels on every update, a nightly job could summarize stock levels for reporting purposes. Here’s a simplified trigger that avoids complex calculations: DELIMITER // -- Trigger to log stock changes after an update on the Products table CREATE TRIGGER SimpleStockLog AFTER UPDATE ON Products FOR EACH ROW BEGIN -- Check if the new stock value is different from the old stock value IF NEW.Stock <> OLD.Stock THEN -- Insert a record into the StockAudit table with the product ID, change type, and quantity changed INSERT INTO StockAudit (ProductID, ChangeType, QuantityChanged) VALUES (NEW.ProductID, 'UPDATE', NEW.Stock - OLD.Stock); END IF; END; // DELIMITER ; Conditional Logic and Business Rules Conditional logic within triggers enables dynamic enforcement of business rules. For example, a trigger can adjust discounts based on stock availability: DELIMITER // -- Trigger to adjust discount based on stock levels after an update on the Products table TRIGGER AdjustDiscount AFTER UPDATE ON Products FOR EACH ROW BEGIN -- Check if the new stock value is greater than 100 IF NEW.Stock > 100 THEN -- Set the discount to 10 if the stock is greater than 100 UPDATE Products SET Discount = 10 WHERE ProductID = NEW.ProductID; ELSE -- Set the discount to 0 if the stock is 100 or less UPDATE Products SET Discount = 0 WHERE ProductID = NEW.ProductID; END IF; END; // DELIMITER ; This dynamic adjustment ensures that promotions align with inventory levels. Conclusion SQL triggers are indispensable for automating tasks, enforcing rules, and maintaining data integrity within a database. While they offer significant benefits, their design and implementation require careful consideration to avoid performance issues and unintended consequences. By adhering to best practices, such as keeping triggers simple, testing thoroughly, and documenting dependencies, developers can harness their full potential. Properly implemented triggers can elevate database management, making operations more efficient and reliable. Hostman provides pre-configured and ready-to-use cloud databases, including cloud MySQL.

Updating data in databases is a critical task when working with MySQL. It involves modifying the values of existing records in a table. Updates can range from modifying fields in a group of rows (or even all rows in a table) to adjusting a specific field in a single row. Understanding the syntax for updating data is essential for effectively working with both local and cloud databases. The key command for modifying records in a MySQL database table is UPDATE. Updates occur sequentially, from the first row to the last. Depending on the type of update, there are two syntax options for the UPDATE statement in MySQL. Syntax for Updating a Single Table UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET assignment_list WHERE where_condition ORDER BY ... LIMIT row_count; Parameters: Required: SET assignment_list: Specifies which columns to modify and how (assignment_list is the list of columns and their new values). Optional: LOW_PRIORITY: If specified, the UPDATE is delayed until no other user is reading data from the table. IGNORE: Ensures the UPDATE continues even if errors occur. Rows with duplicate values in unique key columns are not updated. WHERE where_condition: Specifies the conditions for selecting rows to update. If omitted, all rows in the table will be updated. ORDER BY: Determines the order in which rows are updated. LIMIT row_count: Limits the number of rows updated (row_count specifies the number of rows). This count applies to rows matching the WHERE condition, regardless of whether they are actually modified. Syntax for Updating Multiple Tables UPDATE [LOW_PRIORITY] [IGNORE] table_references SET assignment_list WHERE where_condition; Parameters: table_references: Specifies the tables to update. Changes are applied as defined in assignment_list. ORDER BY and LIMIT are not allowed when updating multiple tables. Other optional parameters (LOW_PRIORITY, IGNORE, WHERE) behave the same as for a single-table update. Note that when updating multiple tables, there is no guarantee that updates will occur in a specific order. Creating a Test Database Let’s create a database for a bookstore that sells rare and antique books from around the world. The table will have four tables: author, genre, book, and sales. CREATE TABLE author ( id INT PRIMARY KEY AUTO_INCREMENT, author_name VARCHAR(50) NOT NULL ); INSERT INTO author (author_name) VALUES ('Leo Tolstoy'), ('Franz Kafka'), ('Nikolai Gogol'), ('William Shakespeare'), ('Homer'); CREATE TABLE genre ( id INT PRIMARY KEY AUTO_INCREMENT, genre_name VARCHAR(30) NOT NULL ); INSERT INTO genre (genre_name) VALUES ('Realist novel'), ('Dystopian novel'), ('Picaresque novel'), ('Epic poetry'); CREATE TABLE book ( book_id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(50), author_id INT NOT NULL, genre_id INT, price DECIMAL(8,2) NOT NULL, amount INT DEFAULT 0, FOREIGN KEY (author_id) REFERENCES author (id), FOREIGN KEY (genre_id) REFERENCES genre (id) ); INSERT INTO book (title, author_id, genre_id, price, amount) VALUES ('Anna Karenina', 1, 1, 650.00, 15), ('The Castle', 2, 2, 570.20, 6), ('Dead Souls', 3, 3, 480.00, 2), ('Iliad', 5, 4, 518.99, 4), ('Odyssey', 5, 4, 518.99, 7); CREATE TABLE sales ( id INT PRIMARY KEY AUTO_INCREMENT, book_id INT NOT NULL, count INT NOT NULL, cost DECIMAL(8,2) NOT NULL, FOREIGN KEY (book_id) REFERENCES book (book_id) ); We will get the following tables. Table: book Here, we have the columns: book_id: Unique book identifier. title: The book's title. author_id: Author identifier (foreign key to author). genre_id: Genre identifier (foreign key to genre). price: Price of the book per unit. amount: Number of books in stock. The genre table will have the following content:  id genre_name 1 Realist novel 2 Dystopian novel 3 Picaresque novel 4 Epic poetry And our author table will look like this: id author_name 1 Leo Tolstoy 2 Franz Kafka 3 Nikolai Gogol 4 William Shakespeare 5 Homer Table: sales The columns here are: id: Unique identifier for the transaction. book_id: Unique book identifier (foreign key to book). count: Number of books sold. cost: Total cost of the purchased books. Data Update Operations Now, having created a sample database, we will demonstrate the execution of various data update operations using the UPDATE statement and other commands in MySQL. 1. Updating All Rows If we omit the WHERE clause in an UPDATE statement, all rows in the table will be updated. For example, suppose there is a promotion in a bookstore where all books are priced at a fixed rate of 500. The query would look like this: UPDATE bookSET price = 500; Resulting Table: If we try to assign a value that is already in a column, MySQL will not update it. If we want to assign a NULL value to a column defined as NOT NULL will, the query will return an error:  Column 'name_column' cannot be null Using the IGNORE parameter forces the value to default: 0 for numeric types, "" for string types, default dates (e.g., 0000 for YEAR, 0000-00-00 00:00:00 for DATETIME). 2. Updating Rows with a Condition Updating all rows is rare; typically, updates are performed based on specific conditions. For instance, to apply a discount on books with fewer than 5 copies in stock: UPDATE book SET price = 300WHERE amount < 5; Resulting Table: 3. Updating Values Using Expressions Columns can be updated using expressions instead of static values. For example, we can apply a 15% discount on Russian classics (author IDs 1 and 3): UPDATE book SET price = price * 0.85WHERE author_id IN (1, 3); Resulting Table: Updates are executed from left to right. For example, the query below increments the amount by 1 and then doubles it: UPDATE book SET amount = amount + 1, amount = amount * 2; Resulting Table: 4. Updating with DEFAULT We can update column values to their default values (DEFAULT), which are defined during the creation or modification of the table. To find out the default values used in our table, we can execute the following query: DESC book; Table Structure: Next, we reset the values of the amount column to its default value. Since the default value for amount is 0, all rows will now have amount set to 0: UPDATE book SET amount = DEFAULT; Resulting Table: 5. Updating Multiple Columns We can update multiple columns in a single query. For example, let's change the price and amount values for rows where book_id < 4: UPDATE book SET price = price * 0.9, amount = amount - 1 WHERE book_id < 4; Resulting Table: 6. Using LIMIT The LIMIT clause allows us to restrict the number of rows to be updated. For instance, we update only the first row where genre_id = 4: UPDATE book SET price = 100 WHERE genre_id = 4 LIMIT 1; The table contains two rows with genre_id equal to 4, but since we specified LIMIT 1, only one will be updated. Resulting Table: Note: The LIMIT N parameter does not guarantee that exactly N rows will be updated—it processes the first N rows matching the WHERE condition, regardless of whether the rows are updated or not. 7. Updating Multiple Tables In MySQL, it is possible to update multiple tables simultaneously. For example, update the amount in the book table and set the author_name to "-" in the author table for specific conditions: UPDATE book, author SET amount = amount + 3, author.author_name = '-' WHERE book.book_id = 4 AND author.id = 4; Resulting book Table: While the author table will look like this: id author_name 1 Leo Tolstoy 2 Franz Kafka 3 Nikolai Gogol 4 William Shakespeare 5 Homer 8. Updating Tables with a Join (INNER JOIN) While performing updates, we can also join tables using the INNER JOIN command. UPDATE book b INNER JOIN author a ON b.author_id = a.idSET b.title = CONCAT(b.title, ' (', a.author_name,')'); Specifying INNER is not mandatory, as this type of join is used by default. We can rewrite the query as follows and get the same result: UPDATE book, author a SET b.title = CONCAT(b.title, ' (', a.author_name,')')WHERE b.author_id = a.id; 9. Updating Tables with a Join (LEFT JOIN) We can also use a LEFT JOIN. In this case, we must specify LEFT JOIN in the query.  For example, we can update the stock quantity of books after a purchase. Let's add two rows to the sales table: INSERT INTO sales (book_id, count, cost) VALUES (1, 3, (SELECT price FROM book WHERE book_id = 1)*3), (3, 1, (SELECT price FROM book WHERE book_id = 3)*1); The store sold 3 copies of 'Anna Karenina' and 1 copy of 'Dead Souls'. Let's execute the query: UPDATE book LEFT JOIN sales on book.book_id = sales.book_idSET amount = amount - countWHERE sales.book_id is not NULL; We can see that now we have less copies of these books: If we try not to use LEFT JOIN, we will encounter the error: Out of range value for column 'amount' at row 3 This happens because amount cannot be negative. Alternatively, if we add IGNORE, the result will be: As we can see, we reduced the quantity in all rows by three books, which is not what we wanted. 10. Updating with CASE, IF, IFNULL, COALESCE When updating a table, it is also possible to use conditional operators such as CASE, IF, and others.The CASE function evaluates a set of conditions and, depending on the result, returns one of the possible outcomes. The syntax for using CASE and WHEN in an UPDATE statement in MySQL is as follows: UPDATE book SET price = CASE genre_id WHEN 1 THEN 100 WHEN 2 THEN 150 ELSE price END; In this case, if the book has genre 1, we set the price to 100, and if the genre is 2, the price is set to 150. The IF function returns one of two values depending on the result of a conditional expression. If the book has genre 4, we decrease its price by 200; otherwise, we leave the price unchanged. UPDATE bookSET price = IF (genre_id = 4, price-200, price); The result: The IFNULL function checks the value of an expression – if it is NULL, a specified value is returned; otherwise, the expression itself is returned. Let's assume that one of the amount values is NULL: Let's check all the values in the amount column, and if any NULL values are found, we will replace them with 0: UPDATE bookSET amount = IFNULL(amount, 0); Resulting Table: The COALESCE function is quite similar to IFNULL. The main difference is that this function can accept multiple values (two or more). Like IFNULL, it returns the first value that is not NULL. To see how this works, let's create a table like this: id col1 col2 col3 col4 1   val12 val13 val14 2     val23 val24 3       val34 And run the query: UPDATE test_tableSET col4 = COALESCE(col1, col2, col3, 'no value'); We will get: id col1 col2 col3 col4 1   val12 val13 val12 2     val23 val23 3       no value 11. Updating with Sorting Sorting can help when updating a field with a unique key. If we want to shift our id values by 1, updating the first row would result in two rows having id = 2, which will cause an error. However, if we add ORDER BY and start updating from the end, the query will execute successfully: UPDATE bookSET book_id=book_id+1  We will get: 12. Updating Based on Data from Other Tables In MySQL, when working with UPDATE, it is possible to use nested SELECT and FROM commands in the WHERE condition. In the following example, we first retrieve the id of the 'Epic poetry' genre, then use the retrieved value to select the rows for updating the table. UPDATE book SET amount = 0 WHERE genre_id = ( SELECT id FROM genre Where genre_name = 'Epic poetry' ); Alternatively, we can select the values that need to be changed using the query: UPDATE book SET price = ( SELECT MIN (cost) FROM sales) WHERE amount < 5; We are updating the price values of all books whose stock quantity is less than 5, setting the price to the minimum selling amount. The minimum selling amount is 480: It is not possible to update the table by selecting values from the same table in a subquery. However, there is a trick we can use – we can join the table with itself: UPDATE book AS book_1 INNER JOIN( SELECT genre_id, MIN(amount) AS min_amount FROM book GROUP BY genre_id ) AS book_2 ON book_1.genre_id = book_2.genre_id SET book_1.amount = book_2.min_amount; In this case, the subquery creates a temporary table for the join and closes it before the UPDATE begins execution. The subquery finds the minimum quantity of books for each genre, which is then used to update the amount column. In our table, only genre 4 has more than one row. The values in both rows should be replaced with the minimum value for that genre, which is 4. We will get: Another option is using SELECT FROM SELECT: UPDATE book AS book_1 SET book_1.price = (SELECT MIN(price) AS min_price FROM ( SELECT price FROM book) as book_2); In this case, a temporary table is also created. However, only a single value is assigned to all rows. Conclusion We have covered the features of using the UPDATE statement in MySQL in as much detail as possible and covered simple scenarios with practical examples.