1

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  | 
Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
H.Otmane
  • 33
  • 6
  • i advice you to read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) before you continue and get stuck in the future with this bad choice. – Raymond Nijland Mar 10 '19 at 16:15

2 Answers2

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

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
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 ..

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107