UPDATE

Syntax:: UPDATE table SET col_name1 = newvalue1 [ , col_name2 = newvalue2, ... ] [ WHERE criteria ]
table: Specifies the table within a database that is to be updated.
col_name1: The column(s) that is to be updated.
newvalue1: The value(s) that is to be updated in the column.
criteria: The condition(s) that determine which of the rows are to be updated.

The UPDATE statement is used to change values in one, or more, or all of the records in a table.

You can use a "cascade update" operation to update records from tables that are in a one-to-many relationship with other tables. A cascade update causes the records in tables that are on the many side of the relationship to be updated when the corresponding record in the one side of the relationship is updated.

The Jet database engine will cascade update if the relationships between tables are configured to enable this option. While Jet SQL gives us a mechanism to establish a relationship (using the CONSTRAINT clause) it does not give us any method to configure relationships to enable cascading operations. However, if a relationship has been configured to enable cascading operations, for example by using Microsoft Access or DAO (Data Access Objects) , the Jet SQL UPDATE and DELETE) statements will cascade.

Examples

Code:


			UPDATE Project

			SET ProjectName = 'Grindstone' 

			WHERE ProjectName = 'Hardwork';

Output:


			(5 row(s) affected)

Explanation:

The UPDATE statement can be used in updating a single field in a table.

Note: The UPDATE doesn't generate a result set. If you want to know which records will be modified, first run a SELECT query that uses the same criteria. If the results are satisfactory, then run the update query.

Language(s): MS SQL Server

Code:


			UPDATE Products

			SET ShippingWeight = '800lbs', ProductName = 'Grindstone MarkII', Price = '$348.71'
 
			WHERE CatalogNumber = 'GS1097';

Output:


			(1 row(s) affected)

Explanation:

The UPDATE statement can also be used to update more than one field in a table.

Language(s): MS SQL Server

Code:


			UPDATE MusicArtists

			SET Instrument = 'violin'
 
			WHERE Instrument = 'fiddle'

			AND Style = 'classical';

Output:


			(1 row(s) affected)

Explanation:

This example updates the 'MusicArtists' table by replacing any occurrence of the instrument 'fiddle' with 'violin' if the music type is 'classical'.

Language(s): MS SQL Server

Code:


			UPDATE TrainFares
 
			SET Fare = Fare * 1.1
 
			WHERE Line = 'Edinburgh' 

			AND Journey = 'single';

Output:


			(4 row(s) affected)

Explanation:

This example updates the 'TrainFares' table to account for a 10% increase in the cost of a single ticket on the Edinburgh line.

Primary links

JetSQL » Statements » UPDATE

Examples

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

See Also: