Updating MySQL table from another table

Question

I have a table called salesinvoiceitems that contains fields as

1. invoiceNumber
2. name
3. qty
4. rate
5. ledgerId

And a balance table with fields as

1. invoiceNumber
2. ledgerId
3. amount

In the balance table I have all the rows containing the invoice numbers. I want to update the amount from salesinvoiceitems table. Amount needs to be calculated as follows:

SUM OF (RATE X QTY) for all the rows of one invoice from salesinvoicetable.

I have tried this but not working:

INSERT INTO balancetable (ledgerId,invoiceNumber,date,company,triggerredby)
SELECT buyerId,invoiceNumber,invoiceDate,company,"salesinvoices" as triggerredby
FROM salesinvoices

Please shed some light.

Answer 1

It's unclear why you are trying to insert when you want to update the balance records. Or did you mean MERGE?

If you do want to update the balance table, you could solve it with a sub-select as follows:

UPDATE BALANCE B
  SET AMOUNT = (SELECT ROUND(SUM(QTY * RATE),2) 
                  FROM SALESINVOICEITEMS S
                 WHERE S.INVOICENUMBER = B.INVOICENUMBER)

The same logic could be used on an insert statement.

If you meant to MERGE the data on the balance table, meaning that you would have to insert or update depending on the row's existence, try checking this link out:

How can I merge two MySql tables?

Answer 2

If you don't want to merge, this might not be the best way of going about it but it should work:

`//Connect To MySQL
mysql_connect("SERVER","USER","PASSWORD");
mysql_select_db("productphotos_userlogins");

//Info Gathering
$infogatherp1 = mysql_query("SELECT * FROM members WHERE name='$myusername'");  
while($infogatherp2 = mysql_fetch_array($infogatherp1)) {
$invoicenumber = $infogatherp2['invoiceNumber'];
$ledgerid = $infogatherp2['ledgerId'];
$amount = $infogatherp2['amount'];
}`

Then you can update the other table with the variables.
Note: the bit in the square brackets([]) is the column name in the table from which you want to get the data.

Hope this helps and good luck!