”;
UPDATE Operation on any database updates one or more records, which are already available in the database. You can update the values of existing records in MySQL using the UPDATE statement. To update specific rows, you need to use the WHERE clause along with it.
Syntax
Following is the syntax of the UPDATE statement in MySQL −
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
You can combine N number of conditions using the AND or the OR operators.
Example
Assume we have created a table in MySQL with name EMPLOYEES as −
mysql> CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT ); Query OK, 0 rows affected (0.36 sec)
And if we have inserted 4 records in to it using INSERT statements as −
mysql> INSERT INTO EMPLOYEE VALUES (''Krishna'', ''Sharma'', 19, ''M'', 2000), (''Raj'', ''Kandukuri'', 20, ''M'', 7000), (''Ramya'', ''Ramapriya'', 25, ''F'', 5000), (''Mac'', ''Mohan'', 26, ''M'', 2000);
Following MySQL statement increases the age of all male employees by one year −
mysql> UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ''M''; Query OK, 3 rows affected (0.06 sec) Rows matched: 3 Changed: 3 Warnings: 0
If you retrieve the contents of the table, you can see the updated values as −
mysql> select * from EMPLOYEE; +------------+-----------+------+------+--------+ | FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | +------------+-----------+------+------+--------+ | Krishna | Sharma | 20 | M | 2000 | | Raj | Kandukuri | 21 | M | 7000 | | Ramya | Ramapriya | 25 | F | 5000 | | Mac | Mohan | 27 | M | 2000 | +------------+-----------+------+------+--------+ 4 rows in set (0.00 sec)
Updating the contents of a table using Python
To update the records in a table in MySQL using python −
-
import mysql.connector package.
-
Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.
-
Create a cursor object by invoking the cursor() method on the connection object created above.
-
Then, execute the UPDATE statement by passing it as a parameter to the execute() method.
Example
The following example increases age of all the males by one year.
import mysql.connector #establishing the connection conn = mysql.connector.connect( user=''root'', password=''password'', host=''127.0.0.1'', database=''mydb'') #Creating a cursor object using the cursor() method cursor = conn.cursor() #Preparing the query to update the records sql = ''''''UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ''M'' '''''' try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database conn.commit() except: # Rollback in case there is any error conn.rollback() #Retrieving data sql = ''''''SELECT * from EMPLOYEE'''''' #Executing the query cursor.execute(sql) #Displaying the result print(cursor.fetchall()) #Closing the connection conn.close()
Output
[(''Krishna'', ''Sharma'', 22, ''M'', 2000.0), (''Raj'', ''Kandukuri'', 23, ''M'', 7000.0), (''Ramya'', ''Ramapriya'', 26, ''F'', 5000.0) ]
”;