Check if row exists before insert or update

Question

public static void StoreGrid(ICAUploadDAO result) { string queryString = "INSERT INTO Result (ModuleGrp, PEM, AdmNo, Name, ICA1, ICA2, ICA3, ICA4, ModuleCode) values ('" + @result.ModuleGrp + "','" + @result.PEM + "','" + @result.AdmNo + "','" + @result.Name + "','" + @result.ICA1 + "','" + @result.ICA2 + "','" + @result.ICA3 + "','" + @result.ICA4 + "','" + @result.ModuleCode + "')"; string connectionString = Properties.Settings.Default.DBConnection; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); command.ExecuteNonQuery(); } } 

I would like to check if the line exists before the insert, if it exists, then she would like to update it. I would be very grateful if someone could help me with this, because I do not know how to do this. Thank you very much.

Answer 1

Just use REPLACE INTO instead of INSERT INTO . But you need to create a unique index for a combination of fields that, in your opinion, make the record the same.

Unfortunately, not all databases support REPLACE INTO , but MySQL and SQLite do.

Answer 2

I had the same problem. Here is what I understood: http://remy.supertext.ch/2010/11/mysqlupdate-and-insert-if-not-exists/

 INSERT INTO wp_postmeta (post_id, meta_key) SELECT ?id, 'page_title' FROM DUAL WHERE NOT EXISTS (SELECT meta_id FROM wp_postmeta WHERE post_id = ?id AND meta_key = 'page_title'); UPDATE wp_postmeta SET meta_value = ?page_title WHERE post_id = ?id AND meta_key = 'page_title'; 

Answer 3

• Just write a sql query to check the number of rows (use the primary key in the where clause).
• If count = 0, execute the update request, if you do not fulfill the insert request.

NOTE. Use one SP to complete the entire task, not to control the code level.