如何检查数据库中的相同数据并替换它? [英] how to check same data exisits in database and replace it?
问题描述
当我插入一个新行时,我需要检查是否存在相同的数据然后更新,如果没有则插入,
这是两步,还是有更好的方法来做到这一点? />
我发现这个SQL查询:
INSERT INTO table SET x = 1, y = 2 ON DUPLICATE KEY 更新 x = x + 1,y = y + 2 其中 pID = ' P001'
但它在INSERT INTO语句中出现错误语法错误。
我在C#WinForm中创建了一个应用程序,我需要在Product表中插入一个新行,同时检查stock_details表中stock_details表中存在的产品名称,如果它存在然后Up产品数量的日期,如果不存在,那么在stock_details表中插入一个新行..
我正在使用带有OLEDB连接的访问数据库....
是它可以在单个sql语句中完成???
在此先感谢....
< blockquote>你使用了一组错误的语句。请阅读有关INSERT和UPDATE语句的内容。
例如
http ://www.w3schools.com/sql/sql_insert.asp [ ^ ]和 http://www.w3schools.com/sql/sql_update.asp [ ^ ]
如果你使用t-sql,你的查询将如下:
IF NOT EXISTS ( SELECT pID < span class =code-keyword> FROM my_table_name WHERE pID = ' p001')
INSERT INTO my_table_name(pID,x,y)
VALUES (' p001', 1 , 2 )
ELSE
更新 my_table_name SET x = x + 1 ,y = y + 2
WHERE pID = ' p001'
你也可以使用MERGE语句。
upd
MERGE INTO ptab AS target
USING ( SELECT pID FROM ptab WHERE pID = ' p001') AS source
ON target .pID = source .pID
WHEN MATCHED
那么 UPDATE SET target .x = 目标 .x + 1 , target .y = target .y + 2
WHEN NOT MATCHED 那么 INSERT (pID,x,y) VALUES (' p001', 1 , 2 );
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....
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);
这篇关于如何检查数据库中的相同数据并替换它?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!