My problem is the following update, which I’m going to make a routine for phpmyadmin.I’ll put a few screenshots of the tables I’m querying.
wp_postmeta Sku
Here is also an example of a SELECT query that combines tables with each other.
SELECT higher stock
The SELECT query works but I haven’t been able to change it to an UPDATE query.
SELECT
´´´
BEGIN
SELECT wp_posts.ID ,wp_posts.post_title
AS Name ,wstock.meta_value
AS Stock ,wsku.meta_value
AS SKU FROM wp_posts
LEFT JOIN wp_postmeta
AS wstock ON wp_posts.ID = wstock.post_id
and wstock.meta_key='_stock'
LEFT JOIN wp_postmeta
AS wsku ON wp_posts.ID = wsku.post_id
AND wsku.meta_key='_sku'
WHERE post_type = 'product'
AND wstock.meta_value != 100
AND wstock.meta_value IS NOT NULL
AND wstock.meta_value > Saldo
AND wsku.meta_value = SKU;
END
´´´
The ID comes from another table which links Sku and Stock.
I have been desperately trying various examples of the work on an UPDATE query,
which would update the online store inventory balance.
´´´
BEGIN
UPDATE
(
SELECT
wp_posts.ID ,
wp_posts.post_title AS Name ,
wstock.meta_value AS Stock ,
wsku.meta_value AS SKU
FROM wp_posts
LEFT JOIN wp_postmeta AS wstock
ON wp_posts.ID = wstock.post_id
AND wstock.meta_key='_stock'
LEFT JOIN wp_postmeta AS wsku
ON wp_posts.ID = wsku.post_id
)
SET wstock.meta_value = Saldo
WHERE
(
post_type = 'product'
AND wsku.meta_key='_sku'
AND wstock.meta_value != 100
AND wstock.meta_value IS NOT NULL
AND wstock.meta_value != 0
AND wsku.meta_value = SKU
AND wstock.meta_value > Saldo
);
END
´´´
It’s what I understand the tests that I have completed that the fault lies in a SELECT selection of UPDATE query. I have tried to change it, but I have not found yet functional form.
Stock = Saldo
Test values:
ID: Name: Stock: Sku:
4104 Dynacore D-2S 4 10070
I have tried to debug it in those values just to run SQL in phpmyadmin window.
3
Answers
Sory I haven't manage to build sample data yet but I'm working on it today after I have tested this.
I'm not sure yet does it break the relation between sku and stock but I hope it will work.
Here some kind sample data…
How to update _age could be this case.
So there is relation between sku and stock not sure yet does it work…