query sql update

[albarelys]

Hello People

I have a script that launch directly on mysql

seems to work .... but not really.

I mean?

the script I need to update prices and quantities on the list.

the script and not from any error, and if I check the products on the database I see that actually I changed them, the problem is that even if you gave them to me changed, both on the site that BO FO do not see the change

you have any idea why?

 

Thanks to all for any help

good evening

[vekia]

make sure that you change correct table (ps_product_shop instead ps_product)

 

what exactly fields you want to change? and what PS version?

[albarelys]

make sure that you change correct table (ps_product_shop instead ps_product)

 

what exactly fields you want to change? and what PS version?

 

 

 

Thanks for the quick response now now I got there that works with ps_product_shop.

is that in BO IN FO ....

Now I'm ceracndo where to put the data amount, because those still will not let me change and in this table where the price is updated correctly there is no table of quantities?

stupid question, I can create them in this root?

 

I got there just five minutes ago sifting through the sql, but really thank you for your help.

You know where you can change the amount?

 

now for the price I did the following:

 

UPDATE ps_product_shop

SET price = '1000 ', minimal_quantity = '1' WHERE id_product = '3 '

 

and works on the price.

I use the version of prestashop is the ver.1.5.4.1

I aveda if I find some other table that has quantity and if all goes well I finished sailors have

 

thank you to already leaving aside only for having responded

thanks thanks thanks

Alberto

[vekia]

quanties are located in the ps_stock_available table, if you want to change quantity - you should edit this table

 

this is because prestashop has got Multishop freature, now all stock informations related to the product are stored in the table that i mentioned (you can see that there is also information about shop id for which products belongs) hope that everything will be clear now

[albarelys]

quanties are located in the ps_stock_available table, if you want to change quantity - you should edit this table

 

this is because prestashop has got Multishop freature, now all stock informations related to the product are stored in the table that i mentioned (you can see that there is also information about shop id for which products belongs) hope that everything will be clear now

 

 

many thanks,

now this is fine.

thanks for the tip that really took away almost all of the problems

 

1st-you can move the price or quantity in one table?

this facilerebbe much everything.

2 - in these tables that have only id_product you can add product reference, so that I can upgrade in 5 minutes, otherwise I should do more script to make sure that the products are aggiornatib by id that puts its prestashop

 

thank you very much for your help,

if you come in Italy at least a dinner waiting for you

thank you thank you

[vekia]

1. you mean that you want to change one table instead of two mentioned by me above? Unfortunately, it isn;t possible, to change the price you have to edit the ps_product_shop, and to change the quantity ps_stock_available. there is no other way, this is how the prestashop works and store informations about products

 

2. you can create simple JOIN in your query, just join table ps_product (ps_product.id_product = ps_stock_available.id_product)

then use where clause: WHERE ps_product.references == 'REFERENCE HERE'

 

it is much easier than editig the core of the ps to support reference field in the ps_stock_available table

[albarelys]

1. you mean that you want to change one table instead of two mentioned by me above? Unfortunately, it isn;t possible, to change the price you have to edit the ps_product_shop, and to change the quantity ps_stock_available. there is no other way, this is how the prestashop works and store informations about products

 

2. you can create simple JOIN in your query, just join table ps_product (ps_product.id_product = ps_stock_available.id_product)

then use where clause: WHERE ps_product.references == 'REFERENCE HERE'

 

it is much easier than editig the core of the ps to support reference field in the ps_stock_available table

 

 

 

Thanks again, and as would be the formula?

 

so if I put

 

 

UPDATE ps_product_shop (ps_product_shop.id_product = ps_stock_available.id_product)

September quantity = '12287 ', price = '12287' WHERE id-product == '1 '

 

(already knew it, are ignorant on the subject) gives me this error

 

# 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 '(ps_product_shop.id_product = ps_stock_available.id_product) September quantity = '1' at line 1

 

can you do me a practical example?

 

thanks again regardless

[vekia]

no, definitely no i just thought that you are "fluent" in sql

no worries my friend, here is an example:

 

UPDATE ps_stock_available AS s
INNER JOIN ps_product_shop AS ps ON ps.id_product = s.id_product
INNER JOIN ps_product AS p ON p.id_product = ps.id_product
SET s.quantity = 500, ps.price='89' WHERE p.reference = 'demo_1'

 

hope everything is clear in the code

if not - feel free to write

[albarelys]

no, definitely no i just thought that you are "fluent" in sql

no worries my friend, here is an example:

 

UPDATE ps_stock_available AS s
INNER JOIN ps_product_shop AS ps ON ps.id_product = s.id_product
INNER JOIN ps_product AS p ON p.id_product = ps.id_product
SET s.quantity = 500, ps.price='89' WHERE p.reference = 'demo_1'

 

hope everything is clear in the code

if not - feel free to write

 

 

Hello.

I tried what is written (thank you for writing the formula I do not know where to start)

unfortunately gives me error, I put in the attached sql says about the error itself, I had to change with reference ID_ product because in a table ps_stock_available `there is no reference (you can not put)?

approffito of your great goodness and wisdom and I ask that I could do.

But even if I can fit without problems inviaire two queries.

the thing I like to do if possible and use reference instead of the ID_ product, since doing the update two or three times a day to work with excel sheet to match the id it would be a little pesante.oggi I tried to do vlookup but I did not succeed ....

 

I thank you again for the great help you have given me so far

thanks and good day

[albarelys]

Hello.

I tried what is written (thank you for writing the formula I do not know where to start)

unfortunately gives me error, I put in the attached sql says about the error itself, I had to change with reference ID_ product because in a table ps_stock_available `there is no reference (you can not put)?

approffito of your great goodness and wisdom and I ask that I could do.

But even if I can fit without problems inviaire two queries.

the thing I like to do if possible and use reference instead of the ID_ product, since doing the update two or three times a day to work with excel sheet to match the id it would be a little pesante.oggi I tried to do vlookup but I did not succeed ....

 

I thank you again for the great help you have given me so far

thanks and good day

 

 

 

Hello, I am again here ehhheh.

I encountered a problem, that is, every day I get a list, which I will elaborate on and then upload. the problem will be that I will have the following, I hope to explain well ....

the file will modify all fields correctly which of course there are, but there are those who will leave the previous amount, thus leaving a positive quantity since the its id has not been touched, the easiest thing I think it send one query that before I resets all quantities, and only after'll send the data with the correct amounts, and those that there are no last price will be left to 0 and I solve the problem.

but I looked everywhere and I could not find an example of how to do to clear the entire column in sql, I have tried to put the id and then the first, the last, but it did not work, how can you do?

Thank you so much for any answers help

Alberto

[vekia]

it's because you have to use p.id_product in WHERE clause (you use only id_product so it is ambiguous)

with joins you can check the reference field from ps_product table

just check my example twice, you will see that im using there p.reference in WHERE clause

[albarelys]

it's because you have to use p.id_product in WHERE clause (you use only id_product so it is ambiguous)

with joins you can check the reference field from ps_product table

just check my example twice, you will see that im using there p.reference in WHERE clause

 

 

 

UPDATE ps_stock_available AS s

INNER JOIN ps_product_shop AS ps ON ps.id_product = s.id_product

INNER JOIN ps_product AS p ON p.id_product = ps.id_product

SET s.quantity = 200, ps.price='6' WHERE p.id_product = '6'

 

 

 

Thank you again for this help, but as you can see the sql I do not understand anything, the formula that I spent time (which I put above) works very well and I thank you again for all the availability to me.

could you help me out of this thing yet?

or put the entire quantity 0 line?

many thanks again for the help

[albarelys]

Hello ....

here I am again to abuse tuia wisdom, it gives me error when launching more than one id

 

the error that I get from this is

 

Show SQL Query pane

 

 

import.php: Missing parameter: import_typeDocumentazione

import.php: Missing parameter: formatDocumentazione

 

 

I launched the query in this way

 

UPDATE ps_stock_available AS s

INNER JOIN ps_product_shop AS ps ON ps.id_product = s.id_product

INNER JOIN ps_product AS p ON p.id_product = ps.id_product

SET s.quantity = 200, ps.price='6' WHERE p.id_product = '315'

SET s.quantity = 200, ps.price='6' WHERE p.id_product = '316'

 

 

and then I tried this way:

UPDATE AS s ps_stock_available

Ps_product_shop AS ps INNER JOIN ON ps.id_product = s.id_product

Ps_product p INNER JOIN AS ON p.id_product = ps.id_product

SET s.quantity = 200, ps.price = '6 'WHERE p.id_product = '5'

UPDATE AS s ps_stock_available

Ps_product_shop AS ps INNER JOIN ON ps.id_product = s.id_product

Ps_product p INNER JOIN AS ON p.id_product = ps.id_product

SET s.quantity = 200, ps.price = '6 'WHERE p.id_product = '6'

 

but always the same result

I have to do? You have no idea?

thanks

Alberto

[vekia]

hmm you have to say something more what you exactly expect, I mean that I noticed that you want to change quantity in bulk...

here are my questions:

1. you want to change the quantity for all products on only for certain of them? if for certain of them, what is the pattern?
   
2. you want to set up the same quantity for all products that you want to change? (200)
   

[albarelys]

hmm you have to say something more what you exactly expect, I mean that I noticed that you want to change quantity in bulk...

here are my questions:

1. you want to change the quantity for all products on only for certain of them? if for certain of them, what is the pattern?
   
2. you want to set up the same quantity for all products that you want to change? (200)
   

 

hello, you have to change in mass, I have three lists to be updated daily

one from about 1000 products, another 800 or so, and 2 others from 1500 pieces

and I have to change only the products that are in the new updated list.

 

for this first you wondered how he could do to clear the line of quantities and set them to 0 before exporting the new updated amount

 

I hope I was clear .....

 

thanks again as always for the answer

 

thanks

 

Alberto

[albarelys]

hello. here I am again

if you can help I saw that this error launching the query on a single table

 

I change everything without problem, the one big problem and that does not allow me to change more than one, it'll take two already

 

UPDATE ps_stock_available

SET quantity = '12287 'WHERE id_product = '315'

SET quantity = '12287 'WHERE id_product = '316'

 

 

 

I get this error

 

# 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 'SET quantity = '12287' WHERE id_product = '316'' at line 3

how can I do to risovere this problem?

thanks as always for any response

 

thanks

Alberto

[vekia]

this query will not work, you cant use two SET definitions, the same with WHERE.

in this case you have to use something like this:

 

SET quantity = 500 WHERE id_product IN (315,316,200,240,400)

[albarelys]

this query will not work, you cant use two SET definitions, the same with WHERE.

in this case you have to use something like this:

 

SET quantity = 500 WHERE id_product IN (315,316,200,240,400)

 

 

Hello, unfortunately this is not good, as each id to a different number on the other

I found online some formulas that can perhaps serve, I wrote to them but the post is very old and certainly do not think that will answer

.

I read that this command can change multiple lines at once but I do not know what should I write in the fields

 

REPLACE table_name (field 1, field 2, ..., field N) VALUES

(value 11, value 12, ..., 1N value)

(value 21, value 22, ..., 2N value)

...

(value M1, M2 value, ..., value MN)

-------------------------------------------------- -----------------------------

other

 

 

REPLACE table (ID, field) VALUES

(1, newValue 1),

(2, newValue 2),

(3, newValue 3),

 

 

I'll have to write in the name table ps_stock_available

then no one else know if I should put commas or another, you think of something?

thanks pereventuali answers

Albarelys