SQL support for (insert if not exists, update if exists) operation.

classic Classic list List threaded Threaded
5 messages Options
Ray Ray
Reply | Threaded
Open this post in threaded view
|

SQL support for (insert if not exists, update if exists) operation.

I want to know if it's possible to do "replace into" like Mysql in Ignite.
Because currently I have these following code to implement this (insert if
not exists, update if exists) logic.
  if not exists (select 1 from t where id = 1)
      insert into t(id, update_time) values(1, getdate())
  else
      update t set update_time = getdate() where id = 1

In mysql, I can use this one liner sql to do the job.

replace into t(id, update_time) values(1, now());




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: SQL support for (insert if not exists, update if exists) operation.

Hello!

Apache Ignite has MERGE INTO
but I've heard that it's not very efficient.

Regards,
--
Ilya Kasnacheev


ср, 5 дек. 2018 г. в 12:27, Ray <[hidden email]>:
I want to know if it's possible to do "replace into" like Mysql in Ignite.
Because currently I have these following code to implement this (insert if
not exists, update if exists) logic.
  if not exists (select 1 from t where id = 1)
      insert into t(id, update_time) values(1, getdate())
  else
      update t set update_time = getdate() where id = 1

In mysql, I can use this one liner sql to do the job.

replace into t(id, update_time) values(1, now());




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Ray Ray
Reply | Threaded
Open this post in threaded view
|

Re: SQL support for (insert if not exists, update if exists) operation.

Hello Ilya,

Thanks for the reply.
I've tried "Merge into", it does not satisfy my needs.

For example,
I create a table with
create table a(a varchar, b varchar,c varchar, primary key(a));

And I inserted one record into table a with
merge into a(a,b) values('1','1');

Then I want to update this record by adding value to column c.
But when I try this sql

merge into a(a,c) values('1','1');

The value of column b is deleted.




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: SQL support for (insert if not exists, update if exists) operation.

Hello!

Then answer seems to be "no" then, I guess.

Regards,
--
Ilya Kasnacheev


ср, 5 дек. 2018 г. в 12:49, Ray <[hidden email]>:
Hello Ilya,

Thanks for the reply.
I've tried "Merge into", it does not satisfy my needs.

For example,
I create a table with
create table a(a varchar, b varchar,c varchar, primary key(a));

And I inserted one record into table a with
merge into a(a,b) values('1','1');

Then I want to update this record by adding value to column c.
But when I try this sql

merge into a(a,c) values('1','1');

The value of column b is deleted.




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Mikael Mikael
Reply | Threaded
Open this post in threaded view
|

Re: SQL support for (insert if not exists, update if exists) operation.

I guess you have to use UPDATE/INSERT, if the records exists most of the time it's not a huge performance killer:

if( update ... == 0)

     insert ....

Mikael

Den 2018-12-05 kl. 11:37, skrev Ilya Kasnacheev:
Hello!

Then answer seems to be "no" then, I guess.

Regards,
--
Ilya Kasnacheev


ср, 5 дек. 2018 г. в 12:49, Ray <[hidden email]>:
Hello Ilya,

Thanks for the reply.
I've tried "Merge into", it does not satisfy my needs.

For example,
I create a table with
create table a(a varchar, b varchar,c varchar, primary key(a));

And I inserted one record into table a with
merge into a(a,b) values('1','1');

Then I want to update this record by adding value to column c.
But when I try this sql

merge into a(a,c) values('1','1');

The value of column b is deleted.




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/