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
insert or replace into Book (ID, Name, TypeID, Level, Seen) values (
(select ID from Book where Name = "SearchName"),
(select Seen from Book where Name = "SearchName"));
Insert into a MySQL table or update if exists
INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE
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
/*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
UPDATE are not the same.
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
OR is not a valid operator in CQL
WHERE or in CAS operation conditionals.
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;
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.
How to Select All Records from One Table That Do Not Exist in Another Table
How to Select the Last Record of a Table in SQL
How to Insert Null into the Datetime Coulmn Instead 1900-01-01 00:00:00.000 in SQL Server
Formatting Numbers by Padding With Leading Zeros in SQL Server
Select Rows Within Last Complete Minute
Postgresql Error: Syntax Error At or Near "Varchar"
How to Connect MySQL Workbench to Running MySQL Inside Docker
Hive Select Data into an Array of Structs
Fetch the Rows Which Have the Max Value for a Column for Each Distinct Value of Another Column
How to Select Three Table With Same Column Name But Different Values
Force a Value of 0 for Non-Existing Value
How to Get Max Date Value of Date Column in Multiple Tables
Add a Column to Specific Position in Mssql Server
Sql: Returning the Most Common Value for Each Person
Sql Query for Values Consisting of Only a Specific Character
Ssis Date in Derived Column as Yyyy-Mm-Dd Format
Extract String Part Between Single Quotes from Formula'S Divisor