phpmyadmin Question posted in
The official documentation can be found here.

How to perform a complex update to a MySQL database? – Phpmyadmin

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

wp_postmeta Stock

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.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Sory I haven't manage to build sample data yet but I'm working on it today after I have tested this.

    BEGIN
UPDATE wp_postmeta 
    (
    SELECT meta_value 
    FROM wp_postmeta 
    WHERE meta_key = '_stock'
    AND meta_value != 100 
    AND meta_value IS NOT NULL 
    AND meta_value != 0 // If amount could be zero have to leave away
    ) 
AS Stock 
    (
    SELECT meta_value 
    FROM wp_postmeta WHERE meta_key = '_sku' 
    AND meta_value = SKU
    )
AS Sku
SET meta_value = Saldo
WHERE wp_postmeta.meta_value = Stock.meta_value
AND wp_postmeta.meta_value = Sku.meta_value;
END

    I'm not sure yet does it break the relation between sku and stock but I hope it will work.


  2. 0

    Here some kind sample data…

    CREATE TABLE sql_test_a 
( 
    ID         VARCHAR2(4000 BYTE), 
    meta_Key VARCHAR2(200 BYTE), 
    meta_value  VARCHAR2(200 BYTE)  
); 


INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('1', '_first', 'Mark');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('2', '_first', 'Mike');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('3', '_first', 'Buster');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('4', '_first', 'Fred');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('5', '_first', 'Stevie');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('6', '_first', 'John');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('1', '_last', 'Snow');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('2', '_last', 'Tyson');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('3', '_last', 'Keaton');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('4', '_last', 'Mercury');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('5', '_last', 'Jobs');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('6', '_last', 'Depp');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('1', '_age', '44');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('2', '_aget', '86');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('3', '_age', '17');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('4', '_age', '33');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('5', '_age', '77');

INSERT INTO sql_test_a (ID, meta_key, meta_value) 
VALUES ('6', '_age', '55');

    How to update _age could be this case.

    Login or Signup to reply.
  3. 0 
    BEGIN
UPDATE wp_postmeta
    (
    SELECT meta_value
    FROM wp_postmeta
    WHERE = meta_key sku = '_sku' 
    LEFT JOIN meta_key stock = '_stock' 
    AND meta_value.stock != 100 
    AND meta_value IS NOT NULL 
    AND meta_value.stock != 0 // If amount could be zero have to leave away
    AND meta_value.sku = SKU
    )
AS Stock
SET meta_value = Saldo
WHERE meta_value.stock > Saldo;
END

    So there is relation between sku and stock not sure yet does it work…

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search