CODEWITHSUNDEEP
mysql
Prat
Prat

Reputation: 519

How to compare values from different tables in Mysql?

I am using Mysql database. I have 3 table-:

table                         Column
tier_price               customer_id,sku(Unique),price,website
catalog_product          entity_id(PK), sku
catalog_product_price    entity_id, value

Now I want to insert those prices from tier_price table into catalog_product_price table for which SKU is not available in catalog_product table & just update those prices in catalog_product_price table for which SKU is already available in catalog_product table.

$query_fetch = "SELECT price,sku FROM tier_price";
$result_fetch = mysql_query($query_fetch);
$num_rows = mysql_num_rows($result_fetch);

$query_fetch1 = "SELECT sku FROM catalog_product";
$result_fetch1 = mysql_query($query_fetch1);
$num_rows1 = mysql_num_rows($result_fetch1);

So how to do that? Is there any way to do that?

Please guide me or provide me some idea.

Upvotes: 0

Views: 478

Answers (1)

user645280
user645280

Reputation: 

insert ignore into catalog_product
  select
    null,
    sku,
  from tier_prices
;

insert into catalog_product_price
  select
    entity_id,
    price
  from tier_prices join
  catalog_product on tier_prices.sku = catalog_product.sku
on duplicate key update
    catalog_product_price.price = tier_prices.price
;

--needs tested--

Note: sku would have to be a unique key in catalog_product and entity_id in catalog_product_price

Updated to match question definition.

Upvotes: 1

Related Questions