Computer Science
UPDATE in SQL
UPDATE in SQL is a command used to modify existing records in a database table. It allows users to change the values of one or more columns in a specific row or set of rows. The UPDATE statement is an essential part of SQL and is used frequently in database management.
Written by Perlego with AI-assistance
Related key terms
1 of 5
4 Key excerpts on "UPDATE in SQL"
eBook - PDFUnderstanding Databases
Concepts and Practice
- Suzanne W. Dietrich(Author)
- 2021(Publication Date)
- Wiley(Publisher)
Although this sounds straightforward, there are many practical concerns, such as the type of quote used to represent strings and date-time formats. For example, some databases use a single quote for strings, another uses double quotes, and yet another uses either single or double quotes. With respect to representing date-time, it is highly recommended to refer to the manual for the specific format of the database that you are using as well as to determine what the default format setting is for the date-time. Update An update statement specifies the table to update. The set clause consists of a list of assignment statements separated by commas that assigns the value specified to the named column of the table. The value is a literal or expression that has a single value or one of the keywords: default or null. There is an optional where clause that restricts the rows of the table to be updated. If the where clause is not present, then all rows of the table are updated. The update statement modifies a table using a set clause for assignment statements with an optional where clause. SYNTAX 6.8 Update Statement Summary: update TABLE-NAME set COLUMN-NAME = VALUE-EXPRESSION, ... [where UPDATE-CONDITION] Example: update technologyArea set aLeadID = '888' where aTitle = 'Database' 6.2 Data Manipulation 159 Multiple columns can be modified with one update statement. Consider the promotion of an employee that requires both a change in title and salary: update employee set eTitle = 'Sr Software Engineer', eSalary = eSalary * 1.10 where eID = '222' The sample update promotes employee with id '222' to a 'Sr Software Engineer' with a 10% raise. This example illustrates that the VALUE-EXPRESSION can be a string literal value or a mathe- matical expression. The VALUE-EXPRESSION of an update can also be an SQL query that returns a single row having a single column. Consider a university example, where a student’s major is stored in the database as a specific code.
eBook - PDF- Mark Shellman, Hassan Afyouni, Philip Pratt, Mary Last, Mark Shellman(Authors)
- 2020(Publication Date)
- Cengage Learning EMEA(Publisher)
Module 6 176 CHANGING EXISTING DATA IN A TABLE The data stored in tables is subject to constant change; prices, addresses, commission amounts, and other data in a database change on a regular basis. To keep data current, you must be able to make these changes to the data in your tables. You can use the UPDATE command to change rows for which a specific condition is true. EXAMPLE 3: Change the last name of customer 616 in the LEVEL1_CUSTOMER table to “Martinez.” The format for the UPDATE command is the word UPDATE, followed by the name of the table to be updated. The next portion of the command consists of the word SET, followed by the name of the column to be updated, an equal sign, and the new value. When necessary, include a WHERE clause to indicate the row(s) on which the change is to occur. The UPDATE command shown in Figure 6-4 changes the last name of customer 616 to Martinez. FIGURE 6-4 UPDATE command to change the last name of customer 616 UPDATE LEVEL1_CUSTOMER SET LAST_NAME = ‘Martinez’ WHERE (CUST_ID = ‘616’); 1 row affected 1 row matched criteria 1 row changed The SELECT command shown in Figure 6-5 shows the data in the table after the change has been made. It is a good idea to use a SELECT command to display the data you changed to verify that the correct update was made. Copyright 2021 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
eBook - PDF- Jan L. Harrington(Author)
- 2003(Publication Date)
- Morgan Kaufmann(Publisher)
Data modification can be performed using the SQL UPDATEcom- mand to change one or more rows. In some cases, you can update the data in a cursor's result table and have those updates propagat- ed back to the underlying base tables. Direct Modification To perform direct data modification using the SQL UPDATE com- mand, you simply include the command in your program. For ex- ample, if the line cost of a purchased item is stored in the host language variable LINE, the order number in WHICH ORDER, and the ISBN in BOOK_NUMB,y o u could update the database column with EXEC SQL UPDATE order iines SET cost iine= iine m WHERE order_numb = which_order AND isbn = book_numb; The preceding statement will update one row in the table because its WHERE predicate contains a primary key expression. To modify multiple rows, you use an UPDATE with a WHERE predicate that iden- tifies multiple rows, such as the following, which increases all retail prices by two percent for books written after 1970: EXEC SQL UPDATE books SET retail_price = retail_price * 1.02 WHERE publication_year > 1970; 232 EMBEDDEDSQL Indicator Variables and Data Modification Indicator variables, which hold information about the result of em- bedded SQL retrievals, can also be used when performing embed- ded SQL modification. Their purpose is to indicate that you want to store a null in a column. For example, assume that the online book- store writes a program that stores new rows in the BOOKS table. Sometimes the user of the program doesn't have complete informa- tion about a book and therefore wants to leave some of the columns null. To do this, the program declares an indicator variable for each col- umn in the table. If the data variable holds a value to be stored, the program sets the indicator variable to 0; if the column is to be left null, the program sets the indicator variable to-1.
- Nong Ye, Teresa Wu(Authors)
- 2014(Publication Date)
- CRC Press(Publisher)
167 Structured Query Language The updated Drugs relation now has the following information (the new record is highlighted in bold): Drug_ID Name Price Description T_001 Tylenol $17.99 100 325-mg tablets bottle T_003 Tylenol $8.99 T_002 Tri_Vitamin $14.99 1500-400-35 solution 50-mL bottle C_001 Claritin $35.99 10 tablets C_002 NULL NULL NULL 8.3.3.2 UPDATE In some situations, we may want to change a value in a record without changing all values in the record. For this purpose, the UPDATE statement can be used. The syntax of the UPDATE statement is UPDATE TableName SET columnName1 = dataValue1 [, columnName2 = dataValue2 ...] [ WHERE searchcondition] The TableName can be the name of the base table or an updatable view. The SET clause specifies the names of one or more columns that are to be updated with a new dataValue . The dataValue must be compatible with the data type for the corresponding column. The optional WHERE clause is used to filter the records to be updated based on the searchCon-dition . If the WHERE clause is omitted, all the records in the table will be updated. Example 8.32 Give all drugs a 15% discount: UPDATE Drugs SET Price = Price *0.85; Example 8.33 Apply a 15% discount to “Tylenol” drugs only: UPDATE Drugs SET Price = Price *0.85 WHERE Name = Tylenol Similar to INSERT , a SELECT-FROM subquery may apply to specify the records to be updated. 8.3.3.3 DELETE The DELETE statement is used to delete the whole record instead of some values of any particular column. The DELETE syntax is applied as DELETE FROM TableName [ WHERE searchCondition ] 168 Developing Windows-Based and Web-Enabled Information Systems The TableName is the name of the base table or an updatable view in which the records are to be deleted. Similar to the UPDATE clause, the optional WHERE clause is used to filter the records to be deleted based on the conditions. If it is omitted, then all the records in the relation will be removed.
Index pages curate the most relevant extracts from our library of academic textbooks. They’ve been created using an in-house natural language model (NLM), each adding context and meaning to key research topics.
