Update with select and delete command

We go on to explain how to make an update from a select and how we can delete records from our database.

Update with select

Very often we have the need to update certain fields that require a prior consultation, for these cases we can do everything in the same statement.

To carry out this sentence we will use an update and within the where or the set we will place the select with the query that we need.

We have to take into account that when the query is part of the set we must select a single row and the same number of columns that are in parentheses next to the set.

The syntax is as follows:

update

If we use the set it would be this other:

update

And we could mix the two cases without any problem.

Here is an example so you can see how it works:

We have to change the salary to half and the commission to set it to 0 for all employees who belong to the department with the largest number of employees.

Update employee set salary=salary/2, commission=0 where department=(select department from employee group by department having count

=(select max(count

) from employee group by department));

As you can see, the sentence can scare a bit, it is easy to understand. We update the data requested and search where for those employees who meet those criteria.

delete

This order allows us to delete one or more rows from a table. In this order the where clause is essential because if we do not put it properly we run the risk of deleting unwanted records. If we do not put a where in the statement, it will delete all the records in the table.

It must be remembered that, like the update, this sentence can also have an added subquery.

The general syntax is quite simple:

delete from tablename where condition;

Here are two examples of using the delete command:

Delete the employees of department 20

delete from employee where department=20;

Delete departments with fewer than four employeesdelete from department where department in (select department from employee group by department having count<4);Reference: We have a to learn to use the standard used for querying databases.