Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Update Query

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      UPDATE Statement

      MySQL UPDATE Statement

      The UPDATE operation on any table updates one or more records, which are already available. 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_reference
      SET column1 = value1, column2 = value2...., columnN = valueN
      WHERE [condition];

      Example

      Assume we have created a table in MySQL with name EMPLOYEES as shown below 
      CREATE TABLE EMPLOYEE(
         FIRST_NAME CHAR(20) NOT NULL,
         LAST_NAME CHAR(20),
         AGE INT,
         SEX CHAR(1),
         INCOME FLOAT
      );
      And if we have inserted 4 records in to it using INSERT statements as 
      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 
      UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M';

      Verification

      If you retrieve the contents of the table, you can see the updated values as 
      select * from EMPLOYEE;

      Output

      Following is the output of the above query
      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

      The IGNORE clause

      When you use the IGNORE clause along with the UPDATE statement
      • The update occurs though an error occurs during the update.
      • If a duplicate conflict occurs in the any of the rows with UNIQUE key constraints that row will not be updated.

      Example

      Assume we have created a table named data using the CREATE statement shown below
      CREATE TABLE data(
         ID INT,
         NAME CHAR(20),
         AGE INT,
         SALARY INT
      );
      Now, let's insert some records into the data table 
      INSERT INTO data values 
      (101, 'Raja', 25, 55452),
      (102, 'Roja', 29, 66458),
      (103, 'Roja', 35, 36944);
      Following query updates the salary value of the employee name roja 
      UPDATE data set SALARY = SALARY+3000 where NAME = 'Roja';

      Verification

      If you verify the content of the table, you will see that the salary of "Roja" increases by 3000.
      SELECT * FROM data;

      Output

      The above query produces the following output
      ID
      NAME
      AGE
      SALARY
      102
      Raja
      25
      55452
      103
      Roja
      29
      69458
      104
      Roja
      35
      39944
      If you use the UPDATE query along with the IGNORE clause and execute the above query again, since there are 2 rows with the required name the query will be ignored.
      UPDATE IGNORE data set SALARY = SALARY+3000 where NAME = 'Roja';
      If you verify the content of the table, you will see that the salary of "Roja" increases by 3000.

      Verification

      SELECT * FROM data;

      Output

      Following is the output of the above mysql query
      ID
      NAME
      AGE
      SALARY
      102
      Raja
      25
      55452
      103
      Roja
      29
      72458
      104
      Roja
      35
      42944

      Updating multiple column values

      Using the UPDATE statement, you can update values of multiple columns as shown below
      UPDATE data set SALARY = SALARY+3000, AGE = AGE+3;
      If you verify the content of the table, you will see that the age increases by 3 and salary increases by 3000.

      Verification

      SELECT * FROM data;

      Output

      The above mysql query will generate the following output
      ID
      NAME
      AGE
      SALARY
      102
      Raja
      28
      58452
      103
      Roja
      32
      78458
      104
      Roja
      38
      48944

      With the ORDER BY clause

      The ORDER BY clause is used to arrange the records of a table based on the specified column we can use this clause along with the UPDATE statement as shown below 
      UPDATE table_name ORDER BY column_name;
      Where table_name is the name of the table and column_name is the name of the column.

      Example

      Following query updates the salary values and arranges the records of the table based on salary 
      UPDATE data SET SALARY = SALARY + 2000 ORDER BY SALARY DESC;
      You can verify the updated values as shown below

      Verification

      SELECT * FROM data;

      Output

      The above query produces the following output
      ID
      NAME
      AGE
      SALARY
      102
      Raja
      28
      60452
      103
      Roja
      32
      80458
      104
      Roja
      38
      50944

      With the LIMIT clause

      You can also use the LIMIYT clause along with the UPDATE statement 
      UPDATE data SET SALARY = SALARY + 2000 ORDER BY SALARY DESC LIMIT 2;
      Since the limit is 2 if you verify the contents of the data table you can observe that only 2 values are updated.

      Verification

      select * FROM data;

      Output

      Following is the output of the above query
      ID
      NAME
      AGE
      SALARY
      102
      Raja
      28
      62452
      103
      Roja
      32
      82458
      104
      Roja
      38
      50944