YouTip

If we need to modify or update data in MySQL, we can use the UPDATE command. ### Syntax Here is the general SQL syntax for updating data in a MySQL table using the UPDATE command: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; **Parameter Description:** * `table_name` is the name of the table you want to update. * `column1`, `column2`, ... are the names of the columns you want to update. * `value1`, `value2`, ... are the new values to replace the old values. * `WHERE condition` is an optional clause used to specify which rows to update. If the `WHERE` clause is omitted, all rows in the table will be updated. **Additional Notes:** * You can update one or more fields at the same time. * You can specify any condition in the WHERE clause. * You can update data in a single table at once. The WHERE clause is very useful when you need to update data for specific rows in a table. ### Examples The following examples demonstrate how to use the UPDATE statement. 1. Update the value of a single column: UPDATE employees SET salary = 60000 WHERE employee_id = 101; 2. Update the values of multiple columns: UPDATE orders SET status = 'Shipped', ship_date = '2023-03-01' WHERE order_id = 1001; 3. Update a value using an expression: UPDATE products SET price = price * 1.1 WHERE category = 'Electronics'; The above SQL statement increases the price of every product in the 'Electronics' category by 10%. 4. Update all rows that meet the condition: UPDATE students SET status = 'Graduated'; The above SQL statement updates the status of all students to 'Graduated'. 5. Update a value using a subquery: UPDATE customers SET total_purchases = ( SELECT SUM(amount) FROM orders WHERE orders.customer_id = customers.customer_id ) WHERE customer_type = 'Premium'; The above SQL statement calculates the total purchase amount for each 'Premium' type customer via a subquery and updates that value into the total_purchases column. > **Note:** When using the UPDATE statement, make sure you provide sufficient conditions to ensure only the rows you intend to modify are updated. If the WHERE clause is omitted, all rows in the table will be updated, which may lead to unpredictable results. * * * ## Updating Data via Command Prompt Below, we will use the UPDATE command with a WHERE clause to update specific data in the `tutorial_tbl` table. The following example will update the `tutorial_title` field value for the record where `tutorial_id` is 3: ## SQL UPDATE Statement: mysql>UPDATE tutorial_tbl SET tutorial_title='Learning C++' WHERE tutorial_id=3; Query OK, 1 row affected (0.01 sec) mysql>SELECT * from tutorial_tbl WHERE tutorial_id=3; +-----------+--------------+---------------+-----------------+ | tutorial_id | tutorial_title | tutorial_author | submission_date | +-----------+--------------+---------------+-----------------+ | 3 | Learning C++ | .COM | 2016-05-06 | +-----------+--------------+---------------+-----------------+ 1 row in set (0.01 sec) From the result, the `tutorial_title` for `tutorial_id` 3 has been modified. * * * ## Updating Data Using PHP Scripts In PHP, the function `mysqli_query()` is used to execute SQL statements. You can use the SQL UPDATE statement with or without a WHERE clause. **Note:** Updating all data in a table without using the WHERE clause should be done with caution. This function has the same effect as executing the SQL statement at the mysql> command prompt. ### Example The following example will update the data in the `tutorial_title` field for the record where `tutorial_id` is 3. ## MySQL UPDATE Statement Test: