CODEWITHSUNDEEP
phpmysqldatabase
Sushant
Sushant

Reputation: 87

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

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);

Upvotes: 0

Views: 1046

Answers (1)

Russell England
Russell England

Reputation: 10241

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}

Upvotes: 1

Related Questions