Insert If Not Exists Else Update

INSERT IF NOT EXISTS ELSE UPDATE?

Have a look at http://sqlite.org/lang_conflict.html.

You want something like:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values
((select ID from Book where Name = "SearchName"), "SearchName", ...);

Note that any field not in the insert list will be set to NULL if the row already exists in the table. This is why there's a subselect for the ID column: In the replacement case the statement would set it to NULL and then a fresh ID would be allocated.

This approach can also be used if you want to leave particular field values alone if the row in the replacement case but set the field to NULL in the insert case.

For example, assuming you want to leave Seen alone:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values (
   (select ID from Book where Name = "SearchName"),
   "SearchName",
    5,
    6,
    (select Seen from Book where Name = "SearchName"));

Insert into a MySQL table or update if exists

Use INSERT ... ON DUPLICATE KEY UPDATE

QUERY:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name="A", age=19

Sqlite: Insert if not exist, Update if exist

Use INSERT OR REPLACE which does exactly what you want : insert a new row when the name does not exists or update otherwise.

UPDATE if exists else INSERT in SQL

The below query will fulfill your requirement.

INSERT INTO `ALLOWANCE` (`EmployeeID`, `Year`, `Month`, `OverTime`,`Medical`,
`Lunch`, `Bonus`, `Allowance`) values (10000001, 2014, 4, 10.00, 10.00,
10.45, 10.10, 40.55) ON DUPLICATE KEY UPDATE `EmployeeID` = 10000001

Insert Data if not exists (from 2 tables) and Update otherwise

You can do it dynamically for all products with out IF's , just addthe conditions required:

/*will insert all the unmatched products*/
INSERT INTO tblBranchProducts  (ProductID, ProductName, ProductCode, Quantity, Location) 
SELECT b.ProductID, a.ProductName, a.ProductCode, b.Quantity, b.DestinationLocation
FROM tblStockMoves b
inner join tblWarehouseProducts a on b.ProductID = a.ProductID
LEFT JOIN tblBranchProducts  c ON(a.productid = b.productid)
where  c.productid is null

And:

/*will update all the matching products*/
update tblBranchProducts a
INNER join tblStockMoves b on a.productid = b.productid
set a.quantity= b.qty

Cassandra insert/update if not exists or field=value

UPDATE my_table SET field_a='test' WHERE id='an-id'
IF NOT EXISTS OR IF field_b='AVALUE';

There are a few of nuances here. First it's important to remember that when doing a compare-and-set (CAS) operation in CQL, the syntax and capabilities between INSERT and UPDATE are not the same.

Case-in-point, the IF NOT EXISTS conditional is valid for INSERT, but not for UPDATE. On the flip-side, IF EXISTS is valid for UPDATE, but not for INSERT.

Secondly, OR is not a valid operator in CQL WHERE or in CAS operation conditionals.

Third, using UPDATE with IF EXISTS short-circuits any subsequent conditionals. So UPDATE can either use IF EXISTS or IF (condition) [ AND (another condition) ], but not both.

Considering these points, it would seem one approach here would be to split the statement into two:

INSERT INTO my_table (id,field_a) VALUES ('a1','test') IF NOT EXISTS;

And:

UPDATE my_table SET field_a='test' WHERE id='an-id' IF field_b='AVALUE';

These are both valid CQL. However, that doesn't really help this situation. An alternative would be to build this logic on the application side. Technically, read-before-write approaches are considered anti-patterns in Cassandra, in-built CAS operations not withstanding due to their use of lightweight transactions.

Perhaps something like SELECT field_a,field_b FROM my_table WHERE id='an-id'; is enough to answer whether it exists as well as what the value of field_b is, thus triggering an additional write? There's a potential for a race condition here, so I'd closely examine the business requirements to see if something like this could work.

---

Related Topics