This is often a more efficient way of filtering (but not always). You can use the EXISTS keyword in the WHERE clause along with a subquery. This query will find the MAX of the fees_required field, find all rows that match that value, and update their fees_paid to 400.Įxample 5 – Update Using an EXISTS in the WHERE Clause You can also run an UPDATE statement and use a subquery in the WHERE clause. Notice that the WHERE clause is outside the brackets, meaning the condition applies to the UPDATE statement and not the SELECT statement. The value it will set it to is the MAX of the fees_required value for all records in the student table. This example will update the fees_required value of the record with a student_id of 5. Note that there is a comma separating the fees_paid = 500 and the fees_required.Īs mentioned in the syntax example earlier, you can use a subquery inside the UPDATE statement to set a value based on a query. This has updated both the fees_paid and fees_required fields for the student record with a student_id of 4. You can update multiple columns in the same table in a single UPDATE statement. This has updated the student record with a student_id of 2, and set the fees_paid value to 100. We’ll update a single column and single row in this example. These examples apply to Oracle, SQL Server, MySQL, and PostgreSQL unless stated otherwise.įirst, we’ll use some sample data in a table called student. Let’s have a look at some examples of the SQL UPDATE statement. In SQL Server and MySQL, you can update a specified number of rows, even if there are more that specify the WHERE clause.īoth the TOP keyword in SQL Server and the LIMIT keyword in MySQL are optional.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |