asp.net : INSERT in oracle table only if the clause WHERE of the UPDATE find zero records

Question

I am developping an asp.net application. I would like to know how to update a field of an oracle table record, and if the record is not present, inserting it.

I have a table with the following fields NAME and SURNAME.

I would like to change the SURNAME to "new_surname" of the record where the NAME equals="name". However, if none of the records in table contains a field NAME equals to name I would like to insert a new record (SURNAME=new_surname and NAME=name).

This is my code :

        OracleConnection connection = new OracleConnection(connectionstring);
        try
        {
            connection.Open();
            OracleCommand command = connection.CreateCommand();
            string sql = "UPDATE TABLE SET SURNAME=\'new_surname\' WHERE NAME=\'name\'";


                command.CommandText = sql;
                command.ExecuteNonQuery();

            connection.Close();
        }
        catch (Exception exp)
        {

        }

Is there an optimal way to do the insert only if the update find zero records matching the "where" clause. I was thinking of first doing a select count of the record matching the "where" clause, and then if I found zero results I would do an insert, and if I found at least one result I would do an update. But I find this solution a little bit heavy.

http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table — Steve, Nov 04 '14 at 13:02
As you're writing your SQL in C#, why can't you write in C# a SQL statement that uses MERGE? — ken2k, Nov 04 '14 at 13:05

score 2 · Accepted Answer · answered Nov 04 '14 at 13:08

Cannot test but you could try this

using(OracleConnection connection = new OracleConnection(connectionstring))
using(OracleCommand command = connection.CreateCommand())
{
    connection.Open();
    string sql = @"MERGE INTO TABLE t USING dual on(name='name')
                   WHEN NOT MATCHED THEN INSERT (name, surname) values ('name', 'new_surname')
                   WHEN MATCHED THEN UPDATE SET surname = 'new_surname'";
    command.CommandText = sql;
    command.ExecuteNonQuery();
}

Codeek · Answer 2 · 2014-11-04T13:19:33.463

0

you can modify the query as :

string sql = @"IF EXISTS (SELECT * FROM \'Given Table\' WHERE NAME=\'name\') THEN
BEGIN
--your update query
END
ELSE
BEGIN
-- your Insert Query
END
END IF";

edited Nov 04 '14 at 13:19

answered Nov 04 '14 at 13:07

Codeek

1,624
1
11
20

1

i'd like to know about the reason for down vote. I am sure I'll learn something new. The reason could help me understand where I was wrong. – Codeek Nov 04 '14 at 13:29
Not my downvote, but are you sure that this syntax is allowed in Oracle? My knowledge is rusty like this article https://community.oracle.com/thread/321710?start=0&tstart=0 – Steve Nov 04 '14 at 13:42
1

Thanks a lot. I definitely learned something new. And I'll check the syntax once I get access to an Oracle system. Thanks again for the reference article. – Codeek Nov 04 '14 at 13:49
Sorry for missing reason for downvote. But yes, it is because the answer is not valid Oracle syntax. It raises error `PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL statement only`. Secondly it makes the database work harder than needed as *every* time it first does a SELECT followed by either UPDATE or INSERT. That is *always* two operations. There are answers combining UPDATE and INSERT that only do two operations *sometimes* as well as the better MERGE answer given by @Steve that does the entire thing in a single SQL operation. – Kim Berg Hansen Nov 04 '14 at 13:55
1

well... thanks a ton. Looks like i learned two new things today. Will try to make sure that answers I give are properly verified before posting them. chao – Codeek Nov 04 '14 at 13:59

asp.net : INSERT in oracle table only if the clause WHERE of the UPDATE find zero records

2 Answers2