Update Parent table from Child Table using SqlCommand

Question

I have 2 tables:

WorkSchedule which has 2 columns WorkScheduleID and WorkScheduleStatus
WorkShiftBid which has 3 columns: WorkShiftBidID, WSBidStatus, WorkScheduleID (foreign key to WorkSchedule table)

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?

Write a stored procedure?

James Cooke
– James Cooke

2020-07-11 08:27:51 +00:00
Commented Jul 11, 2020 at 8:27 — James Cooke
– James Cooke, Commented Jul 11, 2020 at 8:27

Cetin Basoz · Accepted Answer · 2020-07-11 12:33:42Z

4

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;

edited Jul 11, 2020 at 12:33

answered Jul 11, 2020 at 8:30

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

marc_s Over a year ago

If you're using a from WorkSchedule w in your second UPDATE statement, then you must use UPDATE w (using the alias) in T-SQL

addsw Over a year ago

I got this problem: "SqlException: Invalid object name 'WorkShiftBidID'." When I ran, it only updated the WSBidStatus in the WorkShiftBid Table. However, it was unable to update the WorkSchedule Table. I think something went wrong in the second part of the query.

Cetin Basoz Over a year ago

@addsw,probably I wrote your table name wrong. Editing.

Collectives™ on Stack Overflow

Update Parent table from Child Table using SqlCommand

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related