How to insert and update product prices using MySQL query?

Today, we are going to talk about MySQL query for product pricing in Magento 2.

If you ever wanted to insert and update product pricing using MySQL in Magento 2 then you landed to the right page.

You can use below code to insert and update the product prices in Magento 2 through MySQL query. We have made the code as per our requirements. But you can always change it as per your needs.

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);


require __DIR__ . '/app/bootstrap.php';
$bootstrap = \Magento\Framework\App\Bootstrap::create(BP, $_SERVER);
$obj = $bootstrap->getObjectManager();
$state = $obj->get('Magento\Framework\App\State');
$state->setAreaCode('adminhtml');


function readCsvRows($csvFile)
{
    $rows = [];
    $fileHandle = fopen($csvFile, 'r');
    while(($row = fgetcsv($fileHandle, 0, ',', '"', '"')) !== false) {
        $rows[] = $row;
    }
    fclose($fileHandle);
    return $rows;
}

function _getResourceConnection()
{
    global $obj;
    return $obj->get('Magento\Framework\App\ResourceConnection');
}

function _getReadConnection()
{
    return _getConnection('core_read');
}

function _getWriteConnection()
{
    return _getConnection('core_write');
}

function _getConnection($type = 'core_read')
{
    return _getResourceConnection()->getConnection($type);
}

function _getTableName($tableName)
{
    return _getResourceConnection()->getTableName($tableName);
}

function _getAttributeId($attributeCode)
{
    $connection = _getReadConnection();
    $sql = "SELECT attribute_id FROM " . _getTableName('eav_attribute') . " WHERE entity_type_id = ? AND attribute_code = ?";
    return $connection->fetchOne(
        $sql,
        [
            _getEntityTypeId('catalog_product'),
            $attributeCode
        ]
    );
}

function _getEntityTypeId($entityTypeCode)
{
    $connection = _getConnection('core_read');
    $sql        = "SELECT entity_type_id FROM " . _getTableName('eav_entity_type') . " WHERE entity_type_code = ?";
    return $connection->fetchOne(
        $sql,
        [
            $entityTypeCode
        ]
    );
}

$writeConnection = _getWriteConnection();
try {
    $csvFile        = 'csv/priceinevntory.CSV';
    $csvDatas        = readCsvRows($csvFile);
    $headers        = array_shift($csvData);
    foreach($csvDatas as $data) {
        $count   = 0;
        
        $entity_id = $data[0];
        
        $weightValue = $data[1];

        $itemQuntity = $data[2];
        $itemISInStock = 0;
        if($itemQuntity > 0)
        {
            $itemISInStock = 1;
        }
        
        $price1Value = number_format($data[3],4,'.','');
        $price2Value = number_format($data[4],4,'.','');
        $price3Value = number_format($data[5],4,'.','');
        $price4Value = number_format($data[6],4,'.','');


        $storeId = 0;
        $entity_id = $record;


        /* Price */
        $priceAttributeId = _getAttributeId('price');
        $priceSql = "INSERT INTO catalog_product_entity_decimal (attribute_id, store_id, entity_id, value) VALUES (". $priceAttributeId .", ". $storeId .", ". $entity_id .", ". $price1Value .") ON DUPLICATE KEY UPDATE value=".$price1Value;
        $writeConnection->query($priceSql);


        /* Customer Group Price */
        $quntity = number_format(1,4,'.','');
        
        $customerGroupPrice1Sql = "INSERT INTO catalog_product_entity_tier_price ( entity_id, all_groups, customer_group_id, qty, value, website_id) VALUES (". $entity_id .", 0, 1, ". $quntity .",  ". $price1Value .", 0) ON DUPLICATE KEY UPDATE value=".$price1Value;
        $writeConnection->query($customerGroupPrice1Sql);

        $updateCustomerGroupPrice1Sql = "UPDATE catalog_product_index_price cpip SET  cpip.tier_price = ".$price1Value." WHERE  cpip.customer_group_id = 1 AND cpip.entity_id = ".$entity_id;
        $writeConnection->query($updateCustomerGroupPrice1Sql);
    


    
        $customerGroupPrice2Sql = "INSERT INTO catalog_product_entity_tier_price ( entity_id, all_groups, customer_group_id, qty, value, website_id) VALUES (". $entity_id .", 0, 2, ". $quntity .",  ". $price2Value .", 0) ON DUPLICATE KEY UPDATE value=".$price2Value;
        $writeConnection->query($customerGroupPrice2Sql);

        $updateCustomerGroupPrice2Sql = "UPDATE catalog_product_index_price cpip SET  cpip.tier_price = ".$price2Value." WHERE  cpip.customer_group_id = 2 AND cpip.entity_id = ".$entity_id;
        $writeConnection->query($updateCustomerGroupPrice2Sql);
    


    
        $customerGroupPrice3Sql = "INSERT INTO catalog_product_entity_tier_price ( entity_id, all_groups, customer_group_id, qty, value, website_id) VALUES (". $entity_id .", 0, 3, ". $quntity .",  ". $price3Value .", 0) ON DUPLICATE KEY UPDATE value=".$price3Value;
        $writeConnection->query($customerGroupPrice3Sql);

        $updateCustomerGroupPrice3Sql = "UPDATE catalog_product_index_price cpip SET  cpip.tier_price = ".$price3Value." WHERE  cpip.customer_group_id = 3 AND cpip.entity_id = ".$entity_id;
        $writeConnection->query($updateCustomerGroupPrice3Sql);
    


    
        $customerGroupPrice4Sql = "INSERT INTO catalog_product_entity_tier_price ( entity_id, all_groups, customer_group_id, qty, value, website_id) VALUES (". $entity_id .", 0, 4, ". $quntity .",  ". $price4Value .", 0) ON DUPLICATE KEY UPDATE value=".$price4Value;
        $writeConnection->query($customerGroupPrice4Sql);

        $updateCustomerGroupPrice4Sql = "UPDATE catalog_product_index_price cpip SET  cpip.tier_price = ".$price4Value." WHERE  cpip.customer_group_id = 4 AND cpip.entity_id = ".$entity_id;
        $writeConnection->query($updateCustomerGroupPrice4Sql);
        


        /* weight */
        $weightAttributeId = _getAttributeId('weight');
        $weightSql = "INSERT INTO catalog_product_entity_decimal (attribute_id, store_id, entity_id, value) VALUES (". $weightAttributeId .", ". $storeId .", ". $entity_id .", ". $weightValue .") ON DUPLICATE KEY UPDATE value=". $weightValue;
        $writeConnection->query($weightSql);


        /* Stock */
        $stockSql = "UPDATE cataloginventory_stock_item item_stock, cataloginventory_stock_status status_stock SET item_stock.qty = '$itemQuntity', item_stock.is_in_stock = IF('$itemQuntity'>0, 1,0), status_stock.qty = '$itemQuntity', status_stock.stock_status = IF('$itemQuntity'>0, 1,0) WHERE item_stock.product_id = '$entity_id' AND item_stock.product_id = status_stock.product_id";
        $writeConnection->query($stockSql);


        echo $entity_id ." - Successfully Updated <br/>";
    }
} catch (Exception $e) {
    $e->getTraceAsString();
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

<?php

ini_set('display_errors', 1);

ini_set('display_startup_errors', 1);

error_reporting(E_ALL);

require __DIR__ . '/app/bootstrap.php';

$bootstrap = \Magento\Framework\App\Bootstrap::create(BP, $_SERVER);

$obj = $bootstrap->getObjectManager();

$state = $obj->get('Magento\Framework\App\State');

$state->setAreaCode('adminhtml');

function readCsvRows($csvFile)

{

$rows = [];

$fileHandle = fopen($csvFile, 'r');

while(($row = fgetcsv($fileHandle, 0, ',', '"', '"')) !== false) {

$rows[] = $row;

}

fclose($fileHandle);

return $rows;

}

function _getResourceConnection()

{

global $obj;

return $obj->get('Magento\Framework\App\ResourceConnection');

}

function _getReadConnection()

{

return _getConnection('core_read');

}

function _getWriteConnection()

{

return _getConnection('core_write');

}

function _getConnection($type = 'core_read')

{

return _getResourceConnection()->getConnection($type);

}

function _getTableName($tableName)

{

return _getResourceConnection()->getTableName($tableName);

}

function _getAttributeId($attributeCode)

{

$connection = _getReadConnection();

$sql = "SELECT attribute_id FROM " . _getTableName('eav_attribute') . " WHERE entity_type_id = ? AND attribute_code = ?";

return $connection->fetchOne(

$sql,

[

_getEntityTypeId('catalog_product'),

$attributeCode

]

);

}

function _getEntityTypeId($entityTypeCode)

{

$connection = _getConnection('core_read');

$sql = "SELECT entity_type_id FROM " . _getTableName('eav_entity_type') . " WHERE entity_type_code = ?";

return $connection->fetchOne(

$sql,

[

$entityTypeCode

]

);

}

$writeConnection = _getWriteConnection();

try {

$csvFile = 'csv/priceinevntory.CSV';

$csvDatas = readCsvRows($csvFile);

$headers = array_shift($csvData);

foreach($csvDatas as $data) {

$count = 0;

$entity_id = $data[0];

$weightValue = $data[1];

$itemQuntity = $data[2];

$itemISInStock = 0;

if($itemQuntity > 0)

{

$itemISInStock = 1;

}

$price1Value = number_format($data[3],4,'.','');

$price2Value = number_format($data[4],4,'.','');

$price3Value = number_format($data[5],4,'.','');

$price4Value = number_format($data[6],4,'.','');

$storeId = 0;

$entity_id = $record;

/* Price */

$priceAttributeId = _getAttributeId('price');

$priceSql = "INSERT INTO catalog_product_entity_decimal (attribute_id, store_id, entity_id, value) VALUES (". $priceAttributeId .", ". $storeId .", ". $entity_id .", ". $price1Value .") ON DUPLICATE KEY UPDATE value=".$price1Value;

$writeConnection->query($priceSql);

/* Customer Group Price */

$quntity = number_format(1,4,'.','');

$customerGroupPrice1Sql = "INSERT INTO catalog_product_entity_tier_price ( entity_id, all_groups, customer_group_id, qty, value, website_id) VALUES (". $entity_id .", 0, 1, ". $quntity .", ". $price1Value .", 0) ON DUPLICATE KEY UPDATE value=".$price1Value;

$writeConnection->query($customerGroupPrice1Sql);

$updateCustomerGroupPrice1Sql = "UPDATE catalog_product_index_price cpip SET cpip.tier_price = ".$price1Value." WHERE cpip.customer_group_id = 1 AND cpip.entity_id = ".$entity_id;

$writeConnection->query($updateCustomerGroupPrice1Sql);

$customerGroupPrice2Sql = "INSERT INTO catalog_product_entity_tier_price ( entity_id, all_groups, customer_group_id, qty, value, website_id) VALUES (". $entity_id .", 0, 2, ". $quntity .", ". $price2Value .", 0) ON DUPLICATE KEY UPDATE value=".$price2Value;

$writeConnection->query($customerGroupPrice2Sql);

$updateCustomerGroupPrice2Sql = "UPDATE catalog_product_index_price cpip SET cpip.tier_price = ".$price2Value." WHERE cpip.customer_group_id = 2 AND cpip.entity_id = ".$entity_id;

$writeConnection->query($updateCustomerGroupPrice2Sql);

$customerGroupPrice3Sql = "INSERT INTO catalog_product_entity_tier_price ( entity_id, all_groups, customer_group_id, qty, value, website_id) VALUES (". $entity_id .", 0, 3, ". $quntity .", ". $price3Value .", 0) ON DUPLICATE KEY UPDATE value=".$price3Value;

$writeConnection->query($customerGroupPrice3Sql);

$updateCustomerGroupPrice3Sql = "UPDATE catalog_product_index_price cpip SET cpip.tier_price = ".$price3Value." WHERE cpip.customer_group_id = 3 AND cpip.entity_id = ".$entity_id;

$writeConnection->query($updateCustomerGroupPrice3Sql);

$customerGroupPrice4Sql = "INSERT INTO catalog_product_entity_tier_price ( entity_id, all_groups, customer_group_id, qty, value, website_id) VALUES (". $entity_id .", 0, 4, ". $quntity .", ". $price4Value .", 0) ON DUPLICATE KEY UPDATE value=".$price4Value;

$writeConnection->query($customerGroupPrice4Sql);

$updateCustomerGroupPrice4Sql = "UPDATE catalog_product_index_price cpip SET cpip.tier_price = ".$price4Value." WHERE cpip.customer_group_id = 4 AND cpip.entity_id = ".$entity_id;

$writeConnection->query($updateCustomerGroupPrice4Sql);

/* weight */

$weightAttributeId = _getAttributeId('weight');

$weightSql = "INSERT INTO catalog_product_entity_decimal (attribute_id, store_id, entity_id, value) VALUES (". $weightAttributeId .", ". $storeId .", ". $entity_id .", ". $weightValue .") ON DUPLICATE KEY UPDATE value=". $weightValue;

$writeConnection->query($weightSql);

/* Stock */

$stockSql = "UPDATE cataloginventory_stock_item item_stock, cataloginventory_stock_status status_stock SET item_stock.qty = '$itemQuntity', item_stock.is_in_stock = IF('$itemQuntity'>0, 1,0), status_stock.qty = '$itemQuntity', status_stock.stock_status = IF('$itemQuntity'>0, 1,0) WHERE item_stock.product_id = '$entity_id' AND item_stock.product_id = status_stock.product_id";

$writeConnection->query($stockSql);

echo $entity_id ." - Successfully Updated <br/>";

}

} catch (Exception $e) {

$e->getTraceAsString();

}

I hope this code will give you fruitful result. Tell us by commenting if you face any trouble.

Fill the below form if you have any Magento programming need.

Add an image programmatically from any url in Magento 2
[crayon-642165ac3aaf4493024770/] Using above fucntion Images can be imported directly from...
Magento 2 : Override the Category view block file
Override view block using di.xml and add the below code...
How to check list of Layout XML called for a specific page in Magento 2?
You can check a list of called layout XML for...
Magento 2 : Setup PWA from Scratch
Follow the below steps to install and set up PWA...
How to remove leading zero’s from the order, invoice and shipment number in Magento 2?
If you want to remove all leading zero's from order,...

Author Info

Hire A. Patel

Let’s have a call

Fill the form & Get Installation Support