Mysql update or insert if not exists

I have no idea if this is even remotely correct. I have a class where I would like to update the database if the fields currently exist or insert if they do not. The complication is that I am doing a joining 3 tables (set_colors, school_art, baseimage)

Any help would be really great.

Here is what I have:

2 Answers 2

I believe you are looking for the following syntax

Data Tutorial Databases

JOIN people ON data

Easily connect your databases and create powerful visualizations and interactive dashboards in minutes.

New Learning Platform

We’ve launched a new website to help you understand the data principles you need to get answers today.

MySQL prov >INSERT rows after determining whether that row is, in fact, new or already exists.

Below we’ll examine the three different methods and explain the pros and cons of each in turn so you have a firm grasp on how to configure your own statements when prov >INSERTION .

Using INSERT IGNORE

Using INSERT IGNORE effectively causes MySQL to ignore execution errors while attempting to perform INSERT statements. This means that an INSERT IGNORE statement which contains a duplicate value in a UNIQUE index or PRIMARY KEY field does not produce an error, but will instead simply ignore that particular INSERT command entirely. The obvious purpose is to execute a large number of INSERT statements for a combination of data that is both already existing in the database as well as new data coming into the system.

Читайте также:  Как найти директ в инстаграме через телефон

For example, our books table might contain a few records already:

If we have a large batch of new and existing data to INSERT and part of that data contains a matching value for the id field (which is a UNIQUE PRIMARY_KEY in the table), using a basic INSERT will produce an expected error:

On the other hand, if we use INSERT IGNORE , the duplication attempt is ignored and no resulting errors occur:

Using REPLACE

In the event that you wish to actually replace rows where INSERT commands would produce errors due to duplicate UNIQUE or PRIMARY KEY values as outlined above, one option is to opt for the REPLACE statement.

When issuing a REPLACE statement, there are two possible outcomes for each issued command:

  • No existing data row is found with matching values and thus a standard INSERT statement is performed.
  • A matching data row is found, causing that existing row to be deleted with the standard DELETE statement, then a normal INSERT is performed afterward.

For example, we can use REPLACE to swap out our existing record of > of In Search of Lost Time by Marcel Proust with Green Eggs and Ham by Dr. Seuss:

Notice that even though we only altered one row, the result indicates that two rows were affected because we actually DELETED the existing row then INSERTED the new row to replace it.

More information on using REPLACE can be found in the official documentation.

Using INSERT . ON DUPLICATE KEY UPDATE

The alternative (and generally preferred) method for INSERTING into rows that may contain duplicate UNIQUE or PRIMARY KEY values is to use the INSERT . ON DUPLICATE KEY UPDATE statement and clause.

Читайте также:  Startup exe что это

Unlike REPLACE – an inherently destructive command due to the DELETE commands it performs when necessary – using INSERT . ON DUPLICATE KEY UPDATE is non-destructive, in that it will only ever issue INSERT or UPDATE statements, but never DELETE .

For example, we have dec > > record of Green Eggs and Ham and revert it back to the original In Search of Lost Time record instead. We can therefore take our original INSERT statement and add the new ON DUPLICATE KEY UPDATE clause:

Notice that we’re using normal UPDATE syntax (but excluding the unnecessary table name and SET keyword), and only assigning the non-UNIQUE values. Also, although unnecessary for the ON DUPLICATE KEY UPDATE method to function properly, we’ve also opted to utilize user variables so we don’t need to specify the actual values we want to INSERT or UPDATE more than once.

As a result, our > record was properly UPDATED as expected:

More information can be found in the official documentation.

Working with Redshift, BigQuery, MySQL, MongoDB, Postgres, IBM DB2, Oracle?

Easily connect your databases and create powerful visualizations and interactive dashboards in minutes.

I am trying to create a STORED PROCEDURE that will be used to UPDATE a table called machine . This table has three columns ( machine_id , machine_name and reg_id ).

In aforementioned table, reg_id ( INT ) is a column whose values can be changed for a machine_id .

I would like to define a QUERY / PROCEDURE to check if a reg_id already exists in that table. If it does, then UPDATE that row, otherwise INSERT a new row.

Читайте также:  Шаблон шапки и аватарки ютуб

Can someone please help me to write that QUERY / PROCEDURE ?