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 = MagentoFrameworkAppBootstrap::create(BP, $_SERVER);
$obj = $bootstrap->getObjectManager();
$state = $obj->get('MagentoFrameworkAppState');
$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('MagentoFrameworkAppResourceConnection');
}

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.