CODEWITHSUNDEEP
sql-server
user979331
user979331

Reputation: 11841

SQL If record exist update, if not insert

I have an SQL query like so:

cmd = new SqlCommand(@"UPDATE Reservations 
                                    SET ReservationsID = (SELECT PersonID FROM People WHERE Name = @Name) WHERE 
                                    SchedulePersonID = (SELECT SchedulePersonID FROM SchedulePerson WHERE (ScheduleID = (SELECT ScheduleID FROM Schedule WHERE Store_No = @StoreNo)) 
                                    AND LibraryTaskID = @Task)");

What I am trying to do is, if the record does not exists, insert it. if the record does not exist I would like to insert the ReservationsID and SchedulePersonID into that table. I have seen some examples online, but I do not understand them. Please help.

Upvotes: 0

Views: 91

Answers (1)

Joe T.
Joe T.

Reputation: 43

cmd = new SqlCommand(@"UPDATE Reservations 
SET ReservationsID = (SELECT PersonID FROM People WHERE Name = @Name) 
WHERE SchedulePersonID = (SELECT SchedulePersonID FROM SchedulePerson   
WHERE (ScheduleID = 
 (SELECT ScheduleID FROM Schedule WHERE Store_No = @StoreNo)) 
                                    AND LibraryTaskID = @Task)");

Isn't going to do what you want, but the biggest thing is Insert != Update. That is, you must do one or the other as the syntax is different.

Insert Table(Fields...) Values(FieldValues...)
Update Table set Fields=Values,... Conditions

PseudoCode

SqlCommand command = new SqlCommand(selectQueryWithFieldsYouWantToUpdateOrInsertInOneTable,connection);
SqlAdapter adapter = new SqlAdapter(command)
SqlCommandBuilder builder = new SqlCommandBuilder(adapter)
DataSet dataset = adapter.Fill(table,"tableName")
DataTable datatable = dataset.tables["tableName"];
if(datatable.Rows.Count == 1) {
  DataRow row = datatable.rows[0];
  row.Field["fieldName"] = fieldValue;
  adapter.update(datatable);
}
else if (datatable.Rows.Count == 0) {
  DataRow row = datatable.NewRow();
  // row.Field["fieldName"] = fieldValue;
  datatable.Rows.Add(row);
  adapter.update(datatable);
}
else {
  multiple rows match conditions...
}

Upvotes: 1

Related Questions