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
      Replace 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.

       REPLACE Query

      MySQL REPLACE Statement

      In general, if we want to add records into an existing table, we use the MySQL INSERT statement. Likewise, we can also add new records or replace an existing records using the MySQL REPLACE statement. The replace statement is similar to the insert statement.
      The only difference is, while inserting a record using the insert statement if a existing column has a UNIQUE or PRIMARY KEY constraint, if the new record has same value for this column an error will be generated.
      In the case of the REPLACE statement if you try to insert a new column with duplicate value for the column with UNIQUE or PRIMARY KEY constraints the old record will be completely replaced by the new record.

      Syntax

      Following is the syntax of the MySQL REPLACE statement 
      REPLACE INTO table_name (column1, column2, column3,...columnN)
      VALUES (value1, value2, value3,...valueN);
      Where, table_name is the name of the table into which you need to insert data, (column1, column2, column3,...columnN) are the names of the columns and (value1, value2, value3,...valueN) are the values in the record.

      Example

      Let us start with creating a table with name CUSTOMERS in MySQL database with primary key constraint on the ID column as shown below 
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR(20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),
         PRIMARY KEY (ID)
      );
      The following query adds two records into the above created table 
      INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
      (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
      (2, 'Khilan', 25, 'Delhi', 1500.00 );
      Execute the following query to display all the records present in the CUSTOMERS table 
      select * FROM CUSTOMERS;
      Following are the records in CUSTOMERS table
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      Now, let us try to insert another record with ID value 2 
      INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
      (2, 'Kaushik', 23, 'Kota', 2000.00 );
      Since the ID column has a primary key constraint, an error will be generated as shown below 
      ERROR 1062 (23000): Duplicate entry '2' for key 'customers.PRIMARY'
      Now, use the REPLACE statement to replace the existing record in the table 
      REPLACE INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
      (2, 'Kaushik', 20, 'Kota', 2000.00 );

      Output

      Executing the query above will produce the following output 
      Query OK, 2 rows affected (0.01 sec)

      Verification

      Execute the following SELECT statement to verify whether the new record has been replaced or not 
      select * from CUSTOMERS;
      As we can observe the output below, the existing record has been replaced with the new record
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Kaushik
      20
      Kota
      2000.00

      Inserting records using REPLACE statement

      When you use the REPLACE statement to insert a record, if that record doesn't match any existing records in the table, it will be added as a new record.

      Example

      The following query uses REPLACE statement to add three new records into the above CUSTOMERS table 
      REPLACE INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
      (3, 'Chaitali', 25, 'Mumbai', 6500.00 ),
      (4, 'Hardik', 27, 'Bhopal', 8500.00 ),
      (5, 'Komal', 22, 'Hyderabad', 4500.00 );

      Output

      Executing the query above will produce the following output 
      Query OK, 3 rows affected (0.00 sec)
      Records: 3  Duplicates: 0  Warnings: 0

      Verification

      Execute the following query to verify whether the above records has been inserted into CUSTOMERS table or not 
      SELECT * FROM CUSTOMERS;
      As we can observe the CUSTOMERS below, the above records are inserted as new records into the table.
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Kaushik
      20
      Kota
      2000.00
      3
      Chaitali
      25
      Mumbai
      6500.00
      4
      Hardik
      27
      Bhopal
      8500.00
      5
      Komal
      22
      Hyderabad
      4500.00

      Replacing a Record Using a Client Program

      Besides replacing records of a table in a MySQL database with a MySQL query, we can also use a client program to perform the REPLACE operation.

      Syntax

      Following are the syntaxes to use REPLACE query in various programming languages
      PHPNodeJSJavaPython
      To replace a record in a table from MySQL Database through a PHP program we need to execute the Alter statement using the mysqli function query() as 
      $sql="REPLACE INTO TABLE_NAME SET COLUMN_NAME1 = NEW_VALUE, COLUMN_NAME2 = NEW_VALUE...";
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      if($mysqli->connect_errno ) {
         printf("Connect failed: %s", $mysqli->connect_error);
         exit();
      }
      //printf('Connected successfully.');
      $sql = "REPLACE INTO tutorials_tbl SET tutorial_id = 1, tutorial_title = 'Java Tutorial', tutorial_author = 'new_author'";
      if($result = $mysqli->query($sql)){
         printf("Replace statement executed successfully..!  ");
      }
      $q = "SELECT * FROM tutorials_tbl";
      if($res = $mysqli->query($q)){
      printf("Records after replace statement are: ");
         while($row = mysqli_fetch_row($res)){
            print_r ($row);
         }
      }
      if($mysqli->error){
         printf("Failed..!" , $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      Replace statement executed successfully..!  Records after replace statement are: Array
      (
          [0] => 1
          [1] => Java Tutorial
          [2] => new_author
          [3] =>
      )
      Array
      (
          [0] => 2
          [1] => PHP Tut
          [2] => unknown2
          [3] => 2023-08-12
      )
      P