Click here to Skip to main content
12,248,339 members (52,061 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# SQL
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 :
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 4:29am
Edited 2-Feb-13 6:05am
v2

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

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:
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

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);
  Permalink  
v3
Comments
Jayanta Chatterjee 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.
Jayanta Chatterjee 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.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160426.1 | Last Updated 2 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100