Need an update statement that multiplies two column values from diffrent table in mysql

Question

I have 2 tables customer and milk as shown below

customers

customers (cid, name,  price);

milk

milk (mid, customer_id, milk_letters, mprice)

I need an update query to multiply price, milk_letters and store in mprice where customer_id = cid

This is my insert statement that is wrong, I need to change it to update statement.

$cid = $_POST['cid'];
$milk_letters = $_POST['milk_letters'];
$sql = "INSERT INTO milk (customer_id, milk_letters, milk_date)
        VALUES ('$cid','$milk_letters', NOW())";
dbQuery($sql);  
$sql2 = "INSERT INTO milk SELECT SUM(price * milk_letters) as mprice FROM customers , milk WHERE customers.cid = '$cid' and milk.customer_id = '$cid'";
    $result2 = dbQuery($sql2);

take a look at this : http://stackoverflow.com/questions/5222044/column-calculated-from-another-column — Mohsen Kamrani, Feb 22 '14 at 07:48
If you want to modify an existing row, you use `UPDATE`, not `INSERT`. — Barmar, Feb 22 '14 at 08:03

score 1 · Accepted Answer · answered Feb 22 '14 at 07:53

1

You could do this in one query rather than 2

INSERT INTO milk (customer_id, milk_letters, milk_date, mprice)
SELECT {$cid}, {$milk_letters}, NOW(), customers.price * {$milk_letters}
FROM customers
WHERE customers.cid = {$cid}

answered Feb 22 '14 at 07:53

Russell England

9,436
1
27
41

okay next thing i want to do is i want to add all mprice of particular customer means will add one more column total in customers and add mprice where cusoter_id = cid – Sushant Feb 22 '14 at 08:35
Something like UPDATE customers SET total = (SELECT SUM(mprice) FROM milk WHERE milk.cid = customers.cid) - although from a programming point of view its better to calculate this value rather than store it - you could create a view CREATE VIEW customertotals AS SELECT customer.cid, milk.milk_letters * customers.price AS milktotal GROUP BY customer.cid - and then just use SELECT cid, milktotal FROM customertotals – Russell England Feb 23 '14 at 11:34

Need an update statement that multiplies two column values from diffrent table in mysql

1 Answers1