CODEWITHSUNDEEP
sqlsql-serverentity-frameworksqlcommand
addsw
addsw

Reputation: 87

Update Parent table from Child Table using SqlCommand

I have 2 tables:

I want to update the WorkSchedule table from the WorkShiftBid table. So this is roughly how it goes:

I press a button in my website, it reads the current WorkShiftBidID and updates the WSBidStatus to 'Approved'.

However, I want to update WorkScheduleStatus and WSBidStatus in both tables to 'Approved' where WorkScheduleID in both tables is the same.

I came up with this query but it is not working:

com.CommandText = "update WorkShiftBid b, WorkSchedule w" +
                  "set b.WSBidStatus ='Approved' and w.WorkScheduleStatus = 'Approved'" +
                  "where WorkShiftBidID = @id and w.WorkScheduleID = b.WorkScheduleID";
com.Parameters.AddWithValue("@id", id);

How should I change it to work?

Upvotes: 0

Views: 701

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23827

You cannot update 2 tables with a single update command. However you can execute 2 updates in a single batch or send them as 2 batches if you like:

com.CommandText = @"update WorkShiftBid
  set WSBidStatus ='Approved'
  where WorkShiftBidID = @id;

update w
  set WorkScheduleStatus = 'Approved'
from WorkSchedule w
  inner join WorkShiftBid b
     on w.WorkScheduleID = b.WorkScheduleID
  where WorkShiftBidID = @id";

com.Parameters.Add("@id", SqlDbType.Int).Value = id;

Upvotes: 4

Related Questions