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

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.

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

1 solution

Solution 1

Add your solution here

Preview 0

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

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us