SQL command to delete and update data in the database

We can not only add data to the database, but also remove it from there. It is also possible to update the data in the database. Let's consider both cases.

Deleting data from the database (DELETE)

Deletion from the database is done using the "DELETE" command. The function deletes not one row, but several, while choosing a row for deletion according to the logic of the "SELECT" function. That is, in order to delete data from the database, it is necessary to precisely define them. Here is an example SQL command for deleting a single line:

DELETE FROM `USERS` WHERE `ID` = 2 LIMIT 1;

This query will remove one record from the "USERS" table that has a value of "2" in the "ID" column.Please note that at the end of the query there is a limit on the selection "LIMIT 1;" 1 line in size. It could not be set if the "ID" field is "PRIMARY KEY" (primary key, that is, it contains only unique values). But still we recommend setting the limit "LIMIT 1;" in any case, if you intend to delete only one line.Now let's try to delete the data range at once. To do this, it is enough to compose a query, as a result of which several rows will be returned. All these lines will be removed:

DELETE FROM `USERS` WHERE `ID` >= 5;

This query will delete all rows in tables that have a value less than 5 in the "ID" column. If you do not set any "WHERE" condition and "LIMIT" limit, then absolutely all rows in the table will be deleted:

DELETE FROM `USERS`;

On some versions of MySQL, the " DELETE FROM _; " method of deleting all rows may be slower than " TRUNCATE _; "; Therefore, to clear the entire table, it is still better to use "TRUNCATE".

Updating data in the database (UPDATE)

The update function "UPDATE" (translated from English as "UPDATE") is quite often used in website projects. As with the "DELETE" function, the update function will not rest until it has updated all fields that match the conditions if there is no fetch limit. Therefore, it is necessary to set unambiguous conditions so that instead of one row, you do not accidentally update half of the table. Here is an example of using the "UPDATE" command:

UPDATE `USERS` SET `NAME` = 'Deep' WHERE `ID` = 3 LIMIT 1;

In this example, the "USERS" table will be set to "Deep" in the "NAME" column of the row whose "ID" column is set to "3". You can update several columns at once for one record by passing values ​​separated by commas. Let's try to update not only the value in the "NAME" column, but also the value in the "FOOD" column using one query:

UPDATE `USERS` SET `NAME` = 'Deep', `FOOD` = 'Cheese' WHERE `ID` = 3 LIMIT 1;

If you do not set any LIMIT limits and WHERE conditions, then all table records will be updated without exceptions.