The SQL UPDATE Statement. The following SQL statement will update the contactname to 'Juan' for all records where country is 'Mexico': Example. And its Active Records library offers a bunch of functions to form wide range of select queries to read data from database. Also check for codeigniter insert query and codeigniter update query. CodeIgniter Select Query Example: This is the general format for writing select query in code igniter. The preceding description is merely an example of how SELECT. FOR UPDATE works. In MySQL, the specific task of generating a unique identifier actually can.
<?php
// This could be supplied by a user, for example
$firstname = 'fred';
$lastname = 'fox';
// Formulate Query
// This is the best way to perform an SQL query
// For more examples, see mysql_real_escape_string()
$query = sprintf('SELECT firstname, lastname, address, age FROM friends
WHERE firstname='%s' AND lastname='%s',
mysql_real_escape_string($firstname),
mysql_real_escape_string($lastname));
// Perform Query
$result = mysql_query($query);
// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . 'n';
$message .= 'Whole query: ' . $query;
die($message);
}
// Use result
// Attempting to print $result won't allow access to information in the resource
// One of the mysql result functions must be used
// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.
while ($row = mysql_fetch_assoc($result)) {
echo $row['firstname'];
echo $row['lastname'];
echo $row['address'];
echo $row['age'];
}
// Free the resources associated with the result set
// This is done automatically at the end of the script
mysql_free_result($result);
?>
UPDATE statement is used for updating existing rows of a table. It has following syntax where values are set for each column to be updated.
You can run UPDATE statements in MySQL command-line, in a GUI tool or with mysqli_query() in a PHP script.
For this article, we are going to use `employee` table mentioned below. SQL commands for creating the table, inserting data and, truncating are available here.
id | first_name | last_name | job_title | salary | notes |
---|---|---|---|---|---|
1 | Robin | Jackman | Software Engineer | 5500 | |
2 | Taylor | Edward | Software Architect | 7200 | |
3 | Vivian | Dickens | Database Administrator | 6000 | |
4 | Harry | Clifford | Database Administrator | 6800 | |
5 | Eliza | Clifford | Software Engineer | 4750 | |
6 | Nancy | Newman | Software Engineer | 5100 | |
7 | Melinda | Clifford | Project Manager | 8500 | |
8 | Harley | Gilbert | Software Architect | 8000 |
We recommend truncating the table and re-entering data after each example so that you can identify the change clearly (by comparing with original table shown above).
Result sets listed in this article (except for amending values section) have been generated in that way and by executing SELECT statements (SELECT * FROM `employee`) in command-line.
Updating Rows that Match Conditions
Following is how to update the salary of an employee whose ID is 3.
Delete Mysql
It’s optional to have a WHERE clause in UPDATE statements. But not having it can lead to critical errors. For an example, in above query, if WHERE clause was omitted, salary will be set to 6500 for all the employees.
If the company wanted to set the minimum salary to 5500, following query can do the necessary update.
- When executing above query, three rows become eligible to be updated. But MySQL updates only two rows since one’s value is already 5500.
- MySQL returns 2 (number of rows affected) which will be the return value of mysqli_affected_rows() if you ran the query in a PHP script.
Above query could have been written as below by having < operator in place of <= operator which will select only two rows to be updated.
You can specify more than one conditions in WHERE clause using AND, OR and IN() as mentioned in article on SELECT statement.
Insert Mysql
Making Amendments to Existing Values
Sometimes you may not want to replace existing values and only want to make adjustments to existing values. For an example, if you want to increase the salary by 200 of the employee with ID 1, you can use following query.
Following query appends a note to the existing `notes` value after a period and a space. If the current value is NULL, separator is omitted and only the new note is added (it's the behavior of CONCAT_WS() function).
If the column type is numeric, you can use arithmetic operators (+, -, * and /) directly and if it is string, you have to use string functions like CONCAT_WS() and CONCAT().
Setting the Order of Update
MySQL doesn't have a guaranteed order for updates. In cases where order is important, you can specify it like below.
In above query, we increment all the IDs by one and it starts incrementing from the last row (8 becomes 9). If it didn't have this order and MySQL tried to do the increment from first row (making 1 into 2), it will throw an error since ID number 2 already exists (`id` has to be unique since it's the primary key).
Limiting Number of Rows to Update
Select For Update Mysql
You can make MySQL increment the ID of only the last two rows by specifying a LIMIT clause as below.
Using MySQL Update Statement in PHP
Multiple Sql Queries In One
Following PHP script shows how to use an update statement in PHP. Two commented out queries correspond to first two conditions of the if-else block. You can try out all three queries by commenting out two queries at a time.