how to check same data exisits in database and replace it?

Question

1.00/5 (1 vote)

See more:

When I insert a new row, I need to check if same data existing then Update if not then Insert,
which is two steps, or is there a better way to do this?

I found this SQL Queries :

SQL

INSERT INTO table SET x=1, y=2 ON DUPLICATE KEY UPDATE x=x+1, y=y+2 where pID='P001'

But it gives an error "Syntax error in INSERT INTO statement".

I created a application in C# WinForm,in that form i need to insert a new row in Product table and at the same time i check the stock_details table that the product name exist in stock_details table, if it exist then Update that product quantity, if does not exist then insert a new row in stock_details table..
I'm using access database with OLEDB connection....
Is it possible to do in a single sql statement ???

Thanks In Advance....

Posted 2-Feb-13 3:29am

JayantaChatterjee

Updated 2-Feb-13 5:05am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

skydger · Accepted Answer · 2013-02-02T03:57:00

Solution 1

Hi, you're using a wrong set of statements. Please read something about INSERT and UPDATE statements.
For example
http://www.w3schools.com/sql/sql_insert.asp[^] and http://www.w3schools.com/sql/sql_update.asp[^]

If you work with t-sql, your query would be like following:

SQL

IF NOT EXISTS (SELECT pID FROM my_table_name WHERE pID = 'p001')
    INSERT INTO my_table_name (pID, x, y)
    VALUES ('p001', 1, 2)
ELSE
    UPDATE my_table_name SET x = x + 1, y = y + 2
     WHERE pID = 'p001'

You could also use MERGE statement.

upd

SQL

MERGE INTO ptab AS target
USING (SELECT pID FROM ptab WHERE pID = 'p001') AS source
   ON target.pID = source.pID
 WHEN MATCHED
 THEN UPDATE SET target.x = target.x + 1, target.y = target.y + 2
 WHEN NOT MATCHED THEN INSERT(pID, x, y) VALUES ('p001', 1, 2);

Posted 2-Feb-13 3:57am

skydger

Updated 2-Feb-13 4:31am

v3

Comments

JayantaChatterjee 2-Feb-13 10:13am

Sir can You give me MERGE statement of this T-sql query???
cause I'm using it in my C# application so I need it in one liner(like simple sql query)...

skydger 2-Feb-13 10:37am

The first solution could be used in stored procedure.
As for MERGE... It is not a simple query :)
if... else ... sometimes more preferable clause than MERGE which has more complex logic. Besides it's not compatible with SQL Server 2000.

JayantaChatterjee 2-Feb-13 10:52am

Sir, i need a sql query which compatible with every version of Sql, because I'm using it in C# winform application with OLEDB connection.:-(
Is this possible to do this ???

skydger 2-Feb-13 11:35am

Well, if you expect your application to work with any sql engine, then you should implement as simple queries as possible (common to all of the sql engines). The most logic had to be in your C# code. But if you expect to use particular sql engine (for example, all Oracle versions), you should learn more about its versions and consider to use stored procedures.

skydger 2-Feb-13 10:38am

I've updated solution, please check it out.