SQL UDPATE using the WHERE Clause

Remember when we talked about having a unique identifier for each row in every table? You start to see why it is so important to have a unique identifier, or key on each row. That is the column you want to use in your WHERE clause. To make the earlier statement use this value, change the WHERE clause as follows:

UPDATE customer SET city='Seattle' 
WHERE customerID=3

Question: What is the difference?
Answer: What if you had more than one customer with the last name you indicated? You would end up updating both rows, when you really wanted to update only one. By using the the customer ID (or whatever other unique identifier you have set up), you will know that you will be updating one and only one row in the database. It is extremely important that you include the WHERE clause in each and every UPDATE statement that you issue. Make sure that you experiment with the WHERE clause. It will be key to your use of SQL and will affect the UPDATE, DELETE, and SELECT statements for nearly every application you can imagine.

SQL supports rows, not tuples; in particular, it supports row types, a row type constructor, and row value constructors. Rows, unlike tuples, have a left to right ordering to their components. For example, the expressions ROW(1,2) and ROW(2,1), both of which are legitimate row value constructor invocations in SQL, represent two different SQL rows.
Note: The keyword ROW in an SQL row value constructor invocation is optional; in practice, it is almost always omitted. Thanks to that left to right ordering, row components ("fields") in SQL can be identified by ordinal position instead of by name. For example, consider the following row value constructor invocation (actually it is a row literal, though SQL does not use that term):

( 'S1' , 'Smith' , 20 , 'London' )

This row clearly has (among other things) a component with the value 'Smith'; logically speaking, however, we cannot say that component is "the SNAME component", we can only say it is the second component. I should add that rows in SQL always contain at least one component; SQL has no analog of the 0-tuple of the relational model (there is no "0-row"). Recall the example involving the SQL row variable SRV-SQL also supports a row assignment operation. In particular, such assignments are involved (in effect) in SQL UPDATE statements. For example, the following UPDATE statement

UPDATE S
SET STATUS = 20 , CITY = 'London'
WHERE CITY = 'Paris';

is defined to be logically equivalent to this one (note the row assignment in the second line):

UPDATE S
SET (STATUS , CITY ) = ( 20 , 'London' )
WHERE CITY = 'Paris';

SRV-SQL is not exclusively used with Microsoft SQL Server. While it's a common usage, it can potentially be used with other database systems as well. Here's a breakdown of the key points:
SRV-SQL: A Flexible DNS Record Type:

• Purpose: It's a type of DNS (Domain Name System) record that maps service names to hostnames and port numbers.
• Flexibility: It can be used with various services, not just SQL Server.
• Structure: It typically follows this format: _service._proto.name. TTL class SRV priority weight port target

Common Uses with SQL Server:

1. Client Connection: Clients can use SRV-SQL records to discover SQL Server instances without needing to know exact hostnames or ports, simplifying connection setup.
2. High Availability: In failover scenarios, SRV-SQL records can dynamically direct traffic to available instances, aiding in service continuity.

Potential Use with Other Databases:

1. Technically Possible: While not as common, SRV-SQL records could be configured for other database systems that support TCP/IP connections.
2. Implementation Dependent: It depends on the specific database system and its configuration capabilities.

Key Considerations:

1. Compatibility: Not all database systems or client applications may fully support SRV-SQL record usage.
2. Check Documentation: Always consult the documentation of your specific database system and client applications to verify compatibility and configuration instructions.

In Summary:

1. SRV-SQL records offer flexibility in service discovery and management.
2. While commonly associated with Microsoft SQL Server, they can potentially be used with other database systems as well.
3. Compatibility and configuration details vary, so always refer to relevant documentation.

Try this exercise to practice using unique identifiers with the UPDATE statement.
Update Statement - Exercise