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

Mysql – Merge two tables by retaining the previous data – Phpmyadmin

How can I update sales in products table depending on sales of productsUPDATE where products.id = productsUPDATE.id and while retaining the previous data numbers of products?

These are my two tables:

# products     # productsUPDATE
id  Sales      id  Sales   
---|-----|     ---|-----|
1  | 10  |     1  | 40  |  
2  | 11  |     2  | 50  | 
3  | 12  |     3  | 80  |

I try this query but I lose data sales in products table:

UPDATE products 
INNER JOIN productsUPDATE ON products.id = productsUPDATE.id 
SET products.sales = productsUPDATE.sales 
WHERE productsUPDATE.id = products.id

query ouput:

# products     # productsUPDATE
id  Sales      id  Sales   
---|-----|     ---|-----|
1  | 40  |     1  | 40  |  
2  | 50  |     2  | 50  | 
3  | 80  |     3  | 80  |

desired output:

# products      # productsUPDATE
id    Sales     id  Sales   
---|--------|   ---|-----|
1  | 40,10  |   1  | 40  |  
2  | 50,11  |   2  | 50  | 
3  | 80,12  |   3  | 80  |
Tags:

2

Answers


  1. 0

    You can use the following solution using CONCAT_WS:

    UPDATE products 
INNER JOIN productsUPDATE ON products.id = productsUPDATE.id 
SET products.sales = CONCAT_WS(',', products.sales, productsUPDATE.sales)
WHERE productsUPDATE.id = products.id

    Instead of storing the data on the products table, you can also use a VIEW:

    CREATE VIEW products_current AS
    SELECT p.id, CONCAT_WS(',', GROUP_CONCAT(DISTINCT p.Sales), GROUP_CONCAT(pu.Sales SEPARATOR ',')) 
    FROM products p INNER JOIN productsUPDATE pu ON p.id = pu.id GROUP BY p.id;

    demo on dbfiddle.uk

    Login or Signup to reply.
  2. 0

    Seems you want comma separated values 

    UPDATE products 
INNER JOIN productsUPDATE ON products.id = productsUPDATE.id 
SET products.sales = concat(products.sales,',',sproductsUPDATE.sales )

    but you should avoid the use comma separated values you should take a look at normalization ..

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

Back To Top
Search