Edit, Change and Manage Table Data Using Stored ProceduresThere are four main SQL statements every user should know: select, delete, insert and update. The update SQL statement is the command that changes records in the database. For instance, if a user wants to change the first name in the company records, the update SQL statement is used to make that happen. However, implementing an update on a database table can mean complete loss in data integrity. For this reason, database programmers should take care when writing SQL update statements on production servers.
Basic SQL Update Statement SyntaxJust like any other programming language, SQL has a basic syntax to follow when creating a command. The following is the basic syntax for the SQL update statement:
update <table> set <column>=<value> where <clause>The “<table>” is the table name in the database that contains the data the programmer wants to change. The “<column>” is the column that is updated. For instance, if the programmer wants to change the first name, the column entry might be “First_Name.” The “<value>” is what the column is the changed value. The “where” clause is the most important part of the SQL update statement. Without the where clause, the entire table is updated with the changes. This type of accident can cause downtime for the business, and the SQL server may need to be restored from a backup. Entering the wrong logic in the where clause can also cause the same issue, except the wrong table records are updated. Always provide a where clause with SQL update statement commands to avoid downtime on the database server.
A Simple SQL Update Statement ExampleFor this example, consider that the programmer needs to update all customer records that contain a phone number with the 954 area code. The programmer is instructed to update the state in the database with “FL” for all customers with a phone number that has the 954 area code. The following example is how the SQL update statement is created:
update customer set state=’FL’ where phone like ‘%954-%’The “customer” text is the table to update. The “state=’FL’” is the change in the column value. The apostrophe mark is used to indicate a string value in SQL. This is different from other programming languages that require the quotation marks to indicate a string. The where clause is the most important part of the statement. The percent signs are used as a wild card in SQL server. The where clause in this SQL update statement says that any record that contains “954-“ should be updated with the new state value. The programmer could also use something like “area_code=’954’” to indicate that the exact value needs to equal “954.” Before attempting a SQL update statement, ensure the where clause is written properly. Run the update statement on a test server, so if mistakes are made, the data corruption can be fixed without it being an emergency.