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
      Upsert

      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.

      Upsert

      The MySQL UPSERT Operation

      The MySQL UPSERT operation combines INSERT and UPDATE into a single statement, allowing you to insert a new row into a table or update an existing row if it already exists. We can understand in the name (UPSERT) itself, where UP stands for UPDATE and SERT stands for INSERT.
      This tutorial covers three common methods to perform UPSERT operations in MySQL: INSERT IGNORE, REPLACE, and INSERT with ON DUPLICATE KEY UPDATE.

      UPSERT Using INSERT IGNORE

      The INSERT IGNORE statement in MySQL allows you to insert a new record into a table. If a record with the same primary key already exists, it ignores the error and doesn't insert the new record.

      Example

      First, let us create a table with the name COURSES using the following query 
      CREATE TABLE COURSES(
         ID int,
         COURSE varchar(50) primary key,
         COST int
      );
      Here, we are inserting records into the COURSES table 
      INSERT INTO COURSES VALUES 
      (1, "HTML", 3000),
      (2, "CSS", 4000),
      (3, "JavaScript", 6000),
      (4, "Node.js", 10000),
      (5, "React.js", 12000),
      (6, "Angular", 8000),
      (7, "Php", 9000);
      The COURSES table obtained is as follows
      ID
      COURSE
      COST
      6
      Angular
      8000
      2
      CSS
      4000
      1
      HTML
      3000
      3
      JavaScript
      6000
      4
      Node.js
      10000
      7
      Php
      9000
      5
      React.js
      12000
      Now, we attempt to insert a duplicate record using the INSERT INTO statement in the following query
      
      INSERT INTO COURSES VALUES (6, 'Angular', 9000);
      This results in an error because a duplicate record cannot be inserted 
      ERROR 1062 (23000): Duplicate entry 'Angular' for key 'courses.PRIMARY'
      Using INSERT IGNORE
      Now, let us perform the same operation using INSERT IGNORE statement
      
      INSERT IGNORE INTO COURSES VALUES (6, 'Angular', 9000);

      Output

      As we can see in the output below, the INSERT IGNORE statement ignores the error 
      Query OK, 0 rows affected, 1 warning (0.00 sec)

      Verification

      We can verify the COURSES table to see that the error was ignored using the following SELECT query 
      SELECT * FROM COURSES;
      The table obtained is as follows
      ID
      COURSE
      COST
      6
      Angular
      8000
      2
      CSS
      4000
      1
      HTML
      3000
      3
      JavaScript
      6000
      4
      Node.js
      10000
      7
      Php
      9000
      5
      React.js
      12000

      UPSERT Using REPLACE

      The MySQL REPLACE statement first attempts to delete the existing row if it exists and then inserts the new row with the same primary key. If the row does not exist, it simply inserts the new row.

      Example

      Let us replace or update a row in the COURSES table. If a row with COURSE "Angular" already exists, it will update its values for ID and COST with the new values provided. Else, a new row will be inserted with the specified values in the query 
      REPLACE INTO COURSES VALUES (6, 'Angular', 9000);

      Output

      The output for the query above produced is as given below 
      Query OK, 2 rows affected (0.01 sec)

      Verification

      Now, let us verify the COURSES table using the following SELECT query 
      SELECT * FROM COURSES;
      We can see in the following table, the REPLACE statement added a new row after deleting the duplicate row
      ID
      COURSE
      COST
      6
      Angular
      9000
      2
      CSS
      4000
      1
      HTML
      3000
      3
      JavaScript
      6000
      4
      Node.js
      10000
      7
      Php
      9000
      5
      React.js
      12000

      UPSERT Using INSERT with ON DUPLICATE KEY UPDATE

      The INSERT ... ON DUPLICATE KEY UPDATE statement in MySQL attempts to insert a new row. If the row already exists, it updates the existing row with the new values specified in the statement.

      Example

      Here, we are updating the duplicate record using the following query
      
      INSERT INTO COURSES VALUES (6, 'Angular', 9000)
      ON DUPLICATE KEY UPDATE
      ID = 6, COURSE = 'Angular', COST = 20000;

      Output

      As we can see in the output below, no error is generated and the duplicate row gets updated.
      Query OK, 2 rows affected (0.01 sec)

      Verification

      Let us verify the COURSES table using the following SELECT query 
      SELECT * FROM COURSES;
      As we can see the table below, the INSERT INTO... ON DUPLICATE KEY UPDATE statement updated the duplicate record
      ID
      COURSE
      COST
      6
      Angular
      20000
      2
      CSS
      4000
      1
      HTML
      3000
      3
      JavaScript
      6000
      4
      Node.js
      10000
      7
      Php
      9000
      5
      React.js
      12000