I have two tables in my DB. The first table will be used to store the invoice details and the second one was used to store the invoices items based on invoice id.
我的数据库中有两个表。第一个表将用于存储发票详细信息,第二个表用于根据发票ID存储发票项目。
<html>
<body>
<p>1.Table 1 Invocies</p>
<table border='1'>
<tr><td>ID</td><td>Customer Id</td><td>Amount</td><td>Date</td></tr>
<tr><td>1</td><td>1</td><td>1500</td><td>2014-08-05</td></tr>
<tr><td>2</td><td>2</td><td>500</td><td>2014-08-05</td></tr>
<tr><td>3</td><td>1</td><td>500</td><td>2014-08-05</td></tr>
<tr><td>4</td><td>1</td><td>600</td><td>2014-08-05</td></tr>
<tr><td>5</td><td>2</td><td>700</td><td>2014-08-05</td></tr>
</table>
<p>1.Table 2 Items</p>
<table border='1'>
<tr><td>ID</td><td>ProductId</td><td>Price</td><td>Invoice Id</td><td>Quantity</td></tr>
<tr><td>1</td><td>2</td><td>800</td><td>1</td><td>1</td></tr>
<tr><td>2</td><td>1</td><td>800</td><td>1</td><td>1</td></tr>
<tr><td>3</td><td>2</td><td>500</td><td>2</td><td>1</td></tr>
<tr><td>4</td><td>2</td><td>500</td><td>3</td><td>1</td></tr>
<tr><td>5</td><td>2</td><td>600</td><td>4</td><td>1</td></tr>
</table>
</body>
</html>
With invoice Id 1 we have two products and the sum of the quantity & Price=1600 but in table 1 the amount was 1500. I want to update the all table 1 amount value is equal to sum of the quantity & price of the product based on their Invoice Id. How to write query for this?
对于发票Id 1,我们有两个产品,数量和价格的总和= 1600,但在表1中,金额为1500.我想更新所有表1的金额值等于基于产品的数量和价格的总和在他们的发票ID上。怎么写这个查询?
1 个解决方案
#1
0
This should do it:
这应该这样做:
UPDATE Table1 SET Amount = (SELECT SUM(Price) FROM Table2 WHERE Table1.ID = Table2.InvoiceID);
#1
0
This should do it:
这应该这样做:
UPDATE Table1 SET Amount = (SELECT SUM(Price) FROM Table2 WHERE Table1.ID = Table2.InvoiceID);