In this post, we will learn How can I do ‘insert if not exists in MySQL and update if exist where for data or record to add new if it does not exist we can perform using insert into,
Whereas we can make changes in existing data or we can update the existing data by simply using a function as an update where we can update the data in a table by simply a command.
Insert The Data If Does Not Exist
To insert a new record in an existing table in MySQL we use the insert into a method to enter new data for which we have given an example below where we can simply add new data with the help of insert into the method.
Suppose you have a table named “employees” with columns “employee_id” (primary key), “first_name”, “last_name”, and “salary”. To insert a new employee or update an existing one based on their Employee id, you can use the following code and learn from it to gain knowledge:
INSERT INTO employees (employee_ id, first_ name, last_ name, salary) VALUES (1, 'John', 'Doe', 50000) ON DUPLICATE KEY UPDATE first name = VALUES( first_name), last_ name = VALUES( last_name), salary = VALUES(s alary);
In this example, if a row with an employee id equal to 1 does not exist, a new row is inserted with the specified values. If a row with an employee id equal to 1 already exists, the values of the first name and last name, and Salary columns are updated to the specified values.
The On duplicate key clause specifies the columns to update and their new values, using the Value() function to refer to the new values specified in the Insert statement.
Note that this query assumes that the employee id column is a primary key or has a unique index defined on it. If the column is not unique, the Insert statement may result in a duplicate key error, even if the row does not already exist.
UPDATE table_ name SET column1 = value1, column2 = value2, ... WHERE condition;
And for example, have a look at the example given below:
UPDATE employees SET salary = 55000 WHERE employee_id = 1;
Where the salary will be audited to 55000 where the employee id is 1.
To learn more about How can I do ‘insert if not exist in MySQL and update if exist visit: by stack over flow.
To learn more about MySQL tutorials and the solutions of the problems along with concepts and tutorials of solutions list and learn visit: MySQL Tutorials And Problems.