Magento Forum

   
SQL Statement question
 
srmobile
Sr. Member
 
Total Posts:  276
Joined:  2011-10-25
 

Running a SQL statement to update prices in a specific category. I’ve gotten the code from another website and the person claims it works however I am getting the following error message:\

“#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘select product_id from catalog_category_product where category_id = 103)’ at line 4”

The code is as follows:

update catalog_product_entity_decimal
set value 
value*0.9 
where attribute_in 
75 and 
entity_id(select product_id from catalog_category_product 
where category_id 
103);

The reference link is:  http://stackoverflow.com/questions/7772820/update-price-massively-in-magento

I’ve replaced the attribute_in and specified the category I want to update.

The server info is as follows:

Server: Localhost via UNIX socket
Server version: 5.0.77
Protocol version: 10
PhpMyadmin version: 3.4.1

Many thanks in advance,

SR

 
Magento Community Magento Community
Magento Community
Magento Community
 
TreInnova
Guru
 
Avatar
Total Posts:  462
Joined:  2009-02-20
Fano
 
srmobile - 03 March 2013 05:32 AM

Running a SQL statement to update prices in a specific category. I’ve gotten the code from another website and the person claims it works however I am getting the following error message:\

“#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘select product_id from catalog_category_product where category_id = 103)’ at line 4”

The code is as follows:

update catalog_product_entity_decimal
set value 
value*0.9 
where attribute_in 
75 and 
entity_id(select product_id from catalog_category_product 
where category_id 
103);

The reference link is:  http://stackoverflow.com/questions/7772820/update-price-massively-in-magento

I’ve replaced the attribute_in and specified the category I want to update.

The server info is as follows:

Server: Localhost via UNIX socket
Server version: 5.0.77
Protocol version: 10
PhpMyadmin version: 3.4.1


Many thanks in advance,

SR

Hi ,
try with this :

update catalog_product_entity_decimal
set value 
value*0.9 
where attribute_in 
75 and 
entity_id IN (select product_id from catalog_category_product 
where category_id 
103);
Regads,
filippo

 
Magento Community Magento Community
Magento Community
Magento Community
 
srmobile
Sr. Member
 
Total Posts:  276
Joined:  2011-10-25
 

Fantastic, worked like a charm!

There was another slight adjustment needed because the original statement had attribute_in and not attribute_id.

update catalog_product_entity_decimal
set value 
value*0.9 
where attribute_id 
75 and 
entity_id IN (select product_id from catalog_category_product 
where category_id 
103);

Thanks!

SR

 
Magento Community Magento Community
Magento Community
Magento Community
 
srmobile
Sr. Member
 
Total Posts:  276
Joined:  2011-10-25
 

Another quick one:

I have attributes cost (attribute_id = 79)and price (attribute_id = 75),

How would the statement look if I wanted to take the “cost” and then update the “price” without removing the cost value?

update catalog_product_entity_decimal
set value 
attribute_id=79+20 
where attribute_id 
75 and 
entity_id IN (select product_id from catalog_category_product 
where category_id 
103);

The above changes the values to 0 so at least I’m on the right path ( I think?)

I believe a lot of people can benefit from this smile

Thanks!

SR

 
Magento Community Magento Community
Magento Community
Magento Community
 
Ryan Sun _Kuafu
Guru
 
Total Posts:  317
Joined:  2008-11-14
FL
 

update catalog_product_entity_decimal as d1
join catalog_product_entity_decimal 
as d2
on d1
.entity_id d2.entity_id
set d1
.value d2.value +20
where d1
.attribute_id 75 and d2.attribute_id 79 and
d1.entity_id IN (select product_id from catalog_category_product 
where category_id 
103);
its a quick raw sql
this may not work for everyone, e.g. if you have multiple store prices defined for one product

 
Magento Community Magento Community
Magento Community
Magento Community
 
srmobile
Sr. Member
 
Total Posts:  276
Joined:  2011-10-25
 

Thank you very much Ryan, worked great on mysql and was exactly what I was looking for smile

So now I’ve put it into a php script for anyone looking to update pricing by category. Please note your COST and PRICE as well as the actual category ID you would like to apply the changes too.

<?php
// Create connection
 
$con=mysqli_connect("localhost","username","password","database");

// Check connection
 
if (mysqli_connect_errno($con))
   
{
   
echo "Failed to connect to MySQL: " mysqli_connect_error();
   
}
  
mysqli_query
($con,"update catalog_product_entity_decimal as d1
join catalog_product_entity_decimal as d2
on d1.entity_id = d2.entity_id
set d1.value = d2.value +20
where d1.attribute_id = 75 and d2.attribute_id = 79 and
d1.entity_id IN (select product_id from catalog_category_product 
where category_id = 103)"
);


mysqli_close($con);
?>

So now if you wanted to update the price by a %, you would need to modify the following line:

set d1.value d2.value *1.1

I believe that should do a 10% markup.

Thanks again guys!

SR

 
Magento Community Magento Community
Magento Community
Magento Community
 
mehulkandiya
Jr. Member
 
Total Posts:  1
Joined:  2014-02-09
 

Hi,

This was very helpful. I have one more question

Why do we use a PHP script to make changes in the database rather than just run SQL command in phpmyadmin??

Sorry if its a stupid question. rasberry

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
    Back to top