Geek Answers Handbook

Perform an update if a row exists, otherwise insert

I need to update a row of rows in a table, if the update row does not exist in the table, I need to insert this row. I cannot use a unique key, therefore it is not recommended to use ON duplicate KEY UPDATE

I need to achieve something like this

DECLARE count DOUBLE; SELECT count(uid) INTO count FROM Table WHERE column1 ='xxxxx' AND column2='xxxxx'; IF (count=0) THEN --peform insert ELSE --perform update END IF

This is for a high performance application. Any ideas? Code level or query level

FYI: Database - Mysql

+4
source share
6 answers

You can work with a temporary table.

  • Put your data in a temporary table
  • Update the "other" table via JOIN
  • Delete relevant data from temp table
  • Insert the remaining data from the temp table into the main table.

It will be faster than recording by recording if you have a lot of data.

+3
source

That the storage procedure we use may possibly work for you too.

 if not exists (select 1 from Table where column1 ='xxxxx' AND column2='xxxxx') insert into Table ( column1,column2) values ( @xxxx,xxxxx) else update Table
+1
source 
 UPDATE <TABLE> SET COLUMN1 = 'xxxx', COLUMN2 ... WHERE COLUMN1 = ... AND COLUMN2 ... IF @@ROWCOUNT = 0 INSERT INTO <TABLE> (COLUMN1, ...) VALUES ('xxxx', ...)

Make sure you use TRANSACTION ISOLATION LEVEL REPEATABLE READ if the problem is concurrency.

0
source 
 BEGIN TRAN IF EXISTS ( SELECT * FROM Table WITH ( UPDLOCK, SERIALIZABLE ) WHERE CONDITION) BEGIN UPDATE Table SET SOMETHING WHERE CONDITION END ELSE BEGIN INSERT INTO Table(Field1,....) VALUES (Value1,..... ) END COMMIT TRAN

NOTE. The transaction is very good, but using IF EXISTS is not good in the case of an insert / update with verbose queries.

0
source

You can use EXISTS or check if there is a subchannel sub, if its> 0, to find out if the allready line exists

0
source

You can find the helpful REPLACE statement. Its syntax is described here .

-1
source

More articles:


All Articles