SQL Update
The SQL commands UPDATE are used to modify the data that is already in the database. The SQL DELETE command uses a WHERE clause.
SQL UPDATE statement is used to change the data of the records held by tables. Which rows are to be updated, is decided by a condition. To specify the condition, we use the WHERE clause.
UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
EXAMPLES:
UPDATE Customers
SET ContactName=’Juan’
WHERE Country=’Mexico’;
Update Warning!
Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!
Example
UPDATE Customers
SET ContactName=’Juan’;
SQL, which stands for “Structured Query Language,” is a special language that helps you chat with databases. It’s like having a conversation with a database. Imagine you’re talking to your computer, but instead of speaking English, you use SQL to ask questions and get information from the database.
Asking Questions: SQL is like a detective that can find specific information from a database. You can ask it to find and show you particular data, like names of people in a certain department:
SHOW ME the names of people in the IT department.
Changing Stuff: SQL isn’t just for reading data; it’s also for making changes. You can use it to add new things, update existing things, or remove stuff from the database. For example:
ADD a new person named John Doe to the list of customers.
Creating Databases and Tables: Think of SQL as a builder. You can use it to make new databases and decide how tables inside those databases should look. It’s like designing the blueprint for your data house:
BUILD a table for products with spaces for ID, name, and price.
Controlling Who Does What: SQL lets you decide who can enter the data house and who can’t. It’s like having keys to a room, and you can decide who gets a key.
Making Procedures and Views: SQL is also good at creating plans and nice views. It can make lists or charts to show data in a neat way.
SQL is not just another language; it’s the boss of data management. Here’s why it’s important:
Everyone Understands It: SQL is like a universal language for databases. It works with many types of databases, so you don’t need to learn a new language for each one.
It’s Fast: SQL is like a speedy racer. It can find information quickly, even if there’s a lot of data.
Keeps Data Safe: SQL is like a security guard. It makes sure only the right people can see and change the data.
Works for Big and Small: SQL is like a flexible tool. It’s great for small businesses and big companies with lots of data.
To sum it up, SQL is like your best buddy when it comes to dealing with data. It helps you talk to databases easily and do all sorts of important tasks.
Course Video
1.Create a 5 row customer table with customer id, name, address, email, budget and update the budget to 1200 where customer id is 3
Output Before Update
Output After Update
2.Create a table of sales with 5 column and insert data in the column and update product name where ID is 3,5,9 in the table.
Output before update
Output after update on sale_id 3,5,9
3. write a query to Update personId = 1001 on sales.Customer table where customerID = 691, 701, 420 use ADVENTURE2019 database in SQL server
Output before update
Output after Update
4. Write sql query to update title= ‘sir’ where BusinessEntityID in (25,26) on person.person table use ADVENTURE2019 database in SQL server.
Output before Update
Output After Update
5.write a sql query to update bonus = 10000 where BusinessEntityID is 275,276,277 on sales.salesperson table use
ADVENTURE2019 database in SQL server.
Output before update
Output after update
6. Develop a SQL query to add ‘Age’ column in students tables and perform a bulk update operation on the “Age” attribute of students with StudentID 1, 2, 3, and 4, setting their ages to 21.
7. Craft a SQL query to update the price of a jewelry item named ‘Gold Necklace’ to $750.00.
8. Write a SQL query to update the LastName of the student with StudentID 7 to ‘Johnson’.
9. Write a SQL query to increment the Age of all students by 1.
10. Formulate a SQL query to update the MetalType attribute to ‘Platinum’ for all jewelry items whose Price exceeds 500.
YouTube Reference :
1)Introduction to SQL Server in Hindi/Urdu
The SQL UPDATE statement modifies existing records in a table. It is used to update specific fields based on given conditions.
The basic syntax is:
sql
Copy code
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
Example:
sql
Copy code
UPDATE Employees
SET salary = 60000
WHERE employee_id = 101;
- Always use the WHERE clause to avoid updating all rows unintentionally.
- Back up your data before performing updates.
- Test the query on a small dataset to ensure accuracy.
Yes, you can update records in one table based on conditions in another table using a JOIN. Example:
sql
Copy code
UPDATE Employees
SET Employees.salary = 70000
FROM Employees
JOIN Departments ON Employees.dept_id = Departments.dept_id
WHERE Departments.name = ‘Finance’;
- Forgetting the Where clause, which updates all rows.
- Mismatched column data types.
- Syntax errors, such as missing commas between column-value pairs.
Yes, you can update multiple columns by separating them with commas in the SET clause:
sql
Copy code
UPDATE Employees
SET salary = 60000, title = ‘Manager’
WHERE employee_id = 102;
You can set a column to NULL using the UPDATE statement:
sql
Copy code
UPDATE Employees
SET middle_name = NULL
WHERE employee_id = 103;
Yes, but it will update all rows in the table, which can have unintended consequences. Use it cautiously.
Tools like SQL Server Management Studio (SSMS) and MySQL Workbench offer debugging features, including query analyzers and execution plans.
Our SQL UPDATE Online Tutorial provides step-by-step guides and examples for beginners and advanced users.
