Cross Table Update with MSSQL & MYSQL – SQL – Programming


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’
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s