Sql Access Query- Update Row If Exists, Insert If Does Not

SQL access query- Update row if exists, insert if does not

Not in one query but you could do two queries for multiple rows.

In MySQL, the equivalent is (as you already know :)

INSERT INTO Table1 (...)
    VALUES(...)
ON DUPLICATE KEY 
    UPDATE column=column+1
;

or

INSERT INTO Table1 (...)
    ( SELECT ...
        FROM ...
    )
ON DUPLICATE KEY 
    UPDATE column=column+1
;

The second form can be written with two queries as:

UPDATE Table1 
    SET (...) 
    WHERE Column1 = 'SomeValue'
;

INSERT INTO Table1 (...)
    ( SELECT ...
        FROM ...
        WHERE 'SomeValue' NOT IN ( SELECT Column1
                                       FROM Table1 )
    )
;

You could also reverse the order and first insert the new rows and then update all rows if that fits with your data better.

*Note that the IN and NOT IN subqueries could be possibly converted to equivalent JOIN and LEFT JOIN with check for NOT NULL forms.

insert into if not exists ms-access

Instead of VALUES you should use SELECT but even then Access wouldn't allow SELECT without FROM.

So the workaround is to use after FROM a query that returns always exactly 1 row, like:

SELECT MIN(id) FROM table_a

Instead of MIN() you could use FIRST().

So your code should be:

INSERT INTO table_a([name]) 
SELECT @name_
FROM (SELECT MIN(id) FROM table_a) AS t
WHERE NOT EXISTS (SELECT 1 FROM table_a WHERE [name] = @name_);

sql update if row exists, insert if not - in one line

If you're going to be checking for duplicates those columns should be indexed. If the composite key is not unique then you have amgibuity where in some cases you update but in others you insert (otherwise you'd never have duplicate values in the composite key).

Therefore you should create a unique index and use upsert syntax.

Condition for Update vs insert

Need code (VBA or macro) behind a form that determines which action query to run. In VBA something like:

If DCount("*", "tablename", "ExpenseType='" & Me.cbxExpense & "' AND [Month]='" & Me.tbxMonth & "' AND [Year]=" & Me.tbxYear) = 0 Then
    CurrentDb.Execute "INSERT INTO tablename (Expense, [Month], [Year], Cost) VALUES ('" & Me.cbxExpense & "', '" & Me.tbxMonth & "', " & Me.tbxYear & ", " & Me.tbxCost & ")"
Else
    CurrentDb.Execute "UPDATE tablename SET Cost=" & Me.tbxCost & " WHERE Expense='" & Me.cbxExpense & "' AND [Month]='" & Me.tbxMonth & ", [Year]=" & Me.tbxYear
End If

Probably also want some validation code to make sure all four controls have data before executing queries.

The real trick is figuring out what event to put code into - the Cost AfterUpdate will work as long as the other fields have data entered first, otherwise the validation will fail and user will have to re-enter cost.

Could have code that doesn't make each control available until previous value is entered.

Month and Year are reserved words and should not use reserved words as names for anything.

Would be better to save month numbers instead of month names for sorting purposes.

Why updating a value which really should be a calculated aggregation of transaction records?

SQL Server Insert if not exists

instead of below Code

BEGIN
   INSERT INTO EmailsRecebidos (De, Assunto, Data)
   VALUES (@_DE, @_ASSUNTO, @_DATA)
   WHERE NOT EXISTS ( SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA);
END

replace with

BEGIN
   IF NOT EXISTS (SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA)
   BEGIN
       INSERT INTO EmailsRecebidos (De, Assunto, Data)
       VALUES (@_DE, @_ASSUNTO, @_DATA)
   END
END

Updated : (thanks to @Marc Durdin for pointing)

Note that under high load, this will still sometimes fail, because a second connection can pass the IF NOT EXISTS test before the first connection executes the INSERT, i.e. a race condition. See stackoverflow.com/a/3791506/1836776 for a good answer on why even wrapping in a transaction doesn't solve this.

Update or insert data depending on whether row exists in access

As @HansUp pointed out in his comment, there is a good discussion about this here, but to summarize:

The recommended approach is to do a SELECT (or something similar) first to see if the row exists, and then issue an INSERT or UPDATE as required.

---

Related Topics