Here is an interesting case for you, please read patiently to understand the simple update with strange behaviour.
I have a two table with very minimal records and i am joining with referential integrity to update the value from 1st table to the second table's column. the update Query behaved differently when records increased over a time. its very simple update Query and the results are very strange. I will brief the scenario step by step as follow.
Table 1: Warehouse
| WH_Id | Warehouse_Code | Depo_Id | Default | Created_Date |
|---|---|---|---|---|
| 1 | WH01 | 1 | 1 | 2023-01-18 14:10:10 |
| 2 | WH02 | 1 | 1 | 2023-01-10 15:10:10 |
| 3 | WH03 | 2 | 0 | 2025-01-15 16:10:10 |
Table 2: Delivery
| Del_Id | Del_Date | Depo_Id | WH_Id | Product_Id | Qty |
|---|---|---|---|---|---|
| 1 | 2025-01-01 | 1 | NULL | 1 | 10 |
| 2 | 2025-01-01 | 1 | NULL | 2 | 20 |
| 3 | 2025-01-01 | 1 | NULL | 3 | 30 |
| 4 | 2025-01-01 | 1 | NULL | 4 | 40 |
| 5 | 2025-01-01 | 1 | NULL | 5 | 50 |
I need to update the WH_Id in Delivery table joining by Warehouse and Depo_Id is a referencial integrity to join both table.
UPDATE D SET D.WH_Id=W.WH_ID FROM Delivery D
JOIN Warehouse W ON D.Depo_Id=W.Depo_Id and D.Default=1
here is a catch, my update Query fetch 2 record from warehouse table based on additional condition Default=1 for the Depo_id=1 and try to udpate against of 5 records in Delivery table. here is my result of update.
Result 1: Delivery
| Del_Id | Del_Date | Depo_Id | WH_Id | Product_Id | Qty |
|---|---|---|---|---|---|
| 1 | 2025-01-01 | 1 | 1 | 1 | 10 |
| 2 | 2025-01-01 | 1 | 1 | 2 | 20 |
| 3 | 2025-01-01 | 1 | 1 | 3 | 30 |
| 4 | 2025-01-01 | 1 | 1 | 4 | 40 |
| 5 | 2025-01-01 | 1 | 1 | 5 | 50 |
Note: I don't mind whether the update query pick the first or the second row from warehouse table to update in delivery.
Just for a information, if you convert the update Query as select you will see the result multiplied as below
select D., W.WH_Id,W.Warehouse_Code FROM Delivery D*
JOIN Warehouse W ON D.Depo_Id=W.Depo_Id and D.Default=1
| Del_Id | Del_Date | Depo_Id | WH_Id | Product_Id | Qty | WH_Id | Warehouse_Code |
|---|---|---|---|---|---|---|---|
| 1 | 2025-01-01 | 1 | NULL | 1 | 10 | 1 | WH01 |
| 2 | 2025-01-01 | 1 | NULL | 2 | 20 | 1 | WH01 |
| 3 | 2025-01-01 | 1 | NULL | 3 | 30 | 1 | WH01 |
| 4 | 2025-01-01 | 1 | NULL | 4 | 40 | 1 | WH01 |
| 5 | 2025-01-01 | 1 | NULL | 5 | 50 | 1 | WH01 |
| 1 | 2025-01-01 | 1 | NULL | 1 | 10 | 2 | WH02 |
| 2 | 2025-01-01 | 1 | NULL | 2 | 20 | 2 | WH02 |
| 3 | 2025-01-01 | 1 | NULL | 3 | 30 | 2 | WH02 |
| 4 | 2025-01-01 | 1 | NULL | 4 | 40 | 2 | WH02 |
| 5 | 2025-01-01 | 1 | NULL | 5 | 50 | 2 | WH02 |
Volume of record in very low in both a table, Warehouse has 100 Records and DeliveryList has just 50 Record maximum at present.
Here is a surprise and strange behaviour of Query, When the Warehouse table has record up to 80 and the update Query picked 1st record from warehouse to update in Delivery. When the record increased above 80 in Warehouse table same update Query updated differenly as below
(No volume increased in Delivery table only 50 records)
UPDATE D SET D.WH_Id=W.WH_ID FROM Delivery D
JOIN Warehouse W ON D.Depo_Id=W.Depo_Id and D.Default=1
Result 2: Delivery
| Del_Id | Del_Date | Depo_Id | WH_Id | Product_Id | Qty |
|---|---|---|---|---|---|
| 1 | 2025-01-01 | 1 | 1 | 1 | 10 |
| 2 | 2025-01-01 | 1 | 2 | 2 | 20 |
| 3 | 2025-01-01 | 1 | 1 | 3 | 30 |
| 4 | 2025-01-01 | 1 | 2 | 4 | 40 |
You see here the WH_ID got updated alternate row differently and you can compare the Result 1 and Result 2.
What i am confused here is 50 , 100 records are not volume at all. why Query update differently when volume increased.
To ensure my assumption is right, I have increased and decreased the Volume in warehouse table (<80 and >80) and issue is getting reproduced, i tried multiple time with different record count and its constantly reproducing.
I really wanted to understand why SQL update works differently for different record count.