At certain conditions, we have to update one table where its column value actually store in another table. A real scenario happened when you have latest product pricing at a table calledproduct_price_latest, and you want to update the latest pricing to your existing table named product_price_current. Both MSSQL & MYSQL able to solve this kind of problem, I will slowly describe how we got to do it one by one.
Databases/Tables
| Table : product_price_current | ||
| product_id | name | price |
| 1000 | Bicycle | 2000 |
| 1001 | Car | 21000 |
| 1002 | Motorcycle | 7000 |
| 1003 | Boat | 45000 |
| Table: product_price_latest | |
| product_id | price |
| 1000 | 2500 |
| 1001 | 18000 |
| 1002 | 12000 |
| 1003 | 74000 |
Descriptions
so the idea is to replace the current product table price lists with the latest product price list, which located in another table. So after updated, product_id = 1001 (Car)’s price will become 18000, product 1003 (Boat)’s price will become 74000 in product_price_current table.
Comparisons
| Conditions | MSSQL | MYSQL |
| Updating latest price list for all the products | UPDATE ppc SET ppc.price = ppl.price FROMproduct_price_current ppc INNER JOIN ON ppc.product_id = ppl.product_idproduct_price_latest ppl |
UPDATEproduct_price_current ppc INNER JOINproduct_price_latest ppl ON ppc.product_id = ppl.product_id SET ppc.price = ppl.price |
| Updating latest price list for ‘Car’ only | UPDATE ppc SET ppc.price = ppl.price FROMproduct_price_current ppc INNER JOIN ON ppc.product_id = ppl.product_id WHERE product_id = 1001product_price_latest ppl |
UPDATEproduct_price_current ppc INNER JOINproduct_price_latest ppl ON ppc.product_id = ppl.product_id SET ppc.price = ppl.price WHERE product_id = 1001 |
| Updating latest price list for all the product, and give 15% discount for all the latest price lists | UPDATE ppc SET ppc.price = ppl.price * 0.75 FROMproduct_price_current ppc INNER JOIN ON ppc.product_id = ppl.product_idproduct_price_latest ppl |
UPDATEproduct_price_current ppc INNER JOINproduct_price_latest ppl ON ppc.product_id = ppl.product_id SET ppc.price = ppl.price * 0.75 |
| Updating latest price list for all the product, given 15% discount rate for all the items, & adding ‘s’ at last of every product name | UPDATE ppc SET ppc.price = ppl.price * 0.75, ppc.name = ppc.name + ‘s’ FROMproduct_price_current ppc INNER JOIN ON ppc.product_id = ppl.product_idproduct_price_latest ppl |
UPDATEproduct_price_current ppc INNER JOINproduct_price_latest ppl ON ppc.product_id = ppl.product_id SET ppc.price = ppl.price * 0.75, ppc.name = ppc.name + ‘s’ |
