Click here to Skip to main content
11,633,425 members (76,853 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL queries
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 3:29am
Edited 2-Feb-13 5: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 at 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 at 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 at 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 at 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 at 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
0 OriginalGriff 8,658
1 Sergey Alexandrovich Kryukov 8,204
2 Mika Wendelius 6,645
3 F-ES Sitecore 2,321
4 Suvendu Shekhar Giri 2,095


Advertise | Privacy | Mobile
Web03 | 2.8.150728.1 | Last Updated 2 Feb 2013
Copyright © CodeProject, 1999-2015
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