USING UPDATE AND DELETE STATEMENTS WITH TABLE ALIAS IN MSSQL

In Microsoft SQL Server (MSSQL), you can use table aliases when working with UPDATE and DELETE statements just like you would in SELECT statements. Table aliases can help simplify your SQL queries and make them more readable.

Here’s how you can use table aliases with UPDATE and DELETE statements in MSSQL:

UPDATE Statement with Table Alias:

UPDATE alias
SET alias.column1 = value1,
    alias.column2 = value2
FROM table_name AS alias
WHERE alias.column3 = some_condition;

DELETE Statement with Table Alias:

DELETE alias
FROM table_name AS alias
WHERE alias.column1 = some_condition;

Example:

Let’s create some sample data.

CREATE TABLE exam_scores (
    student_id INT,
	course VARCHAR(25),
    exam_score INT
);

insert into exam_scores (student_id, course, exam_score) values (1, 'Physics', 80);
insert into exam_scores (student_id, course, exam_score) values (2, 'Maths', 70);
insert into exam_scores (student_id, course, exam_score) values (3, 'Chemistry', 65);
insert into exam_scores (student_id, course, exam_score) values (4, 'Physics', 34);
insert into exam_scores (student_id, course, exam_score) values (1,'Maths', 75);
insert into exam_scores (student_id, course, exam_score) values (6, 'Chemistry' , 34);
insert into exam_scores (student_id, course, exam_score) values (3, 'Maths', 55);
insert into exam_scores (student_id, course, exam_score) values (8, 'Chemistry', 76);
insert into exam_scores (student_id, course, exam_score) values (9, 'Chemistry', 56);
insert into exam_scores (student_id, course, exam_score) values (10, 'Physics', 67);

Let’s say you want to update the exam_score of a particular student_id.

UPDATE es
SET es.exam_score = 100
FROM exam_scores AS es
WHERE es.student_id = 10;

In this example:

• es is an alias for the exam_scores table.
• The UPDATE statement uses the alias es to reference columns in the SET clause and the WHERE clause.

The UPDATE statement with an alias allows you to write more concise and readable code, especially when dealing with complex queries involving multiple tables.

Remember to replace the table and column names with your actual table and column names, and adjust the conditions in the WHERE clause based on your specific requirements.

Output:

Or if you want to delete records from a table with an alias:

DELETE es
FROM exam_scores AS es
WHERE es.student_id = 10;

In this example:

• ‘es’ is an alias for the exam_scores table
• The DELETE statement uses the alias ‘es’ to reference the table from which rows should be deleted.
• The WHERE clause specifies the condition based on which rows should be deleted.

Output:

Summary

Using an alias in the DELETE statement can be particularly useful when working with complex queries involving joins or subqueries, as it helps make the SQL code more readable and concise.

Remember to replace the table and column names with your actual table and column names, and adjust the conditions in the WHERE clause based on your specific requirements.