SQL is required. Note chapter 16th to update and insert data. SQL is required.

Source: Internet
Author: User

SQL is required. Note chapter 16th to update and insert data. SQL is required.
16.1 update data

To UPDATE (modify) The data in the table, you can use the UPDATE statement. UPDATE can be used in two ways:
(1) update specific rows in the table
(2) update all rows marked with red
Do not omit the WHERE clause: Be careful when using UPDATE. If you do not pay attention to it, all rows in the table will be updated.
The basic UPDATE statement consists of three parts:
(1) The table to be updated
(2) column names and their new values
(3) determine the filtering conditions for the rows to be updated
Do not omit the WHERE clause
Update a single column

UPDATE CustomersSET cust_email = 'kim@thetoystore.com'WHERE cust_id = '1000000005';

Update multiple columns

UPDATE CustomersSET cust_contact = 'Sam Roberts',     cust_email = 'sam@toyland.com'WHERE cust_id = '1000000006';

Use a subquery in an UPDATE statement: You can use a subquery in an UPDATE statement to UPDATE column data that can be retrieved using a SELECT statement.
To delete the value of a column, you can set it to NULL.

UPDATE CustomersSET cust_email = NULLWHERE cust_id = '1000000005';
16.2 delete data

To delete data from a table, use the DELECT statement. You can use DELETE in two ways:
(1) Delete A specific row from the table
(2) Delete all rows from the table
Delete a row

DELETE FROM CustomersWHERE cust_id = '1000000006';

DELETE does not require column names or wildcards. DELETE deletes the entire row instead of the column. To delete a specified column, use the UPDATE statement.
DELETE table content rather than table: DELETE statements DELETE rows from the table, or even DELETE all rows in the table. However, DELETE does not DELETE the table itself.
Faster deletion: If you want to DELETE all rows from a TABLE, do not use DELETE. You can use the truncate table statement to perform the same operation, but it is faster (because no data changes are recorded ).

16.3 principles of updating and deleting

Use of UPDATE or DELETE should follow the following conventions:
(1)Unless you plan to UPDATE and DELETE each row, do not use an UPDATE or DELETE statement without the WHERE clause.
(2) ensure that each table has a primary key and use it as much as possible in the WHERE clause (you can specify the primary key, multiple values, or value ranges ).
(3) Before using the WHERE clause for an UPDATE or DELETE statement, you should first use the SELECT clause for testing to ensure that it filters the correct records to prevent incorrect WHERE clause.
(4) use a database that enforces integrity of reference, so that DBMS will not be allowed to delete rows with data associated with other tables.
(5) Some DBMS allows the database administrator to apply constraints to prevent the execution of UPDATE or DELETE without the WHERE clause.
Use with caution: SQL does not have the undo button, so you should use UPDATE and DELETE with caution. Otherwise, you will find that you have updated or deleted the wrong data.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.