updating a table fields where fields in table A = fields in table B

HELP! here is my problem i dont know what is going wrong I have 开发者_如何学C2 tables one of sales and other of transactions, the transaction table is the same as the sales table but is very detailed the sales table is just the total sale... well in the sales table i have some sales in 0 .. i want to update the details sales from the transactions so that in both tables are 0.. now here is the update command

UPDATE transaction SET total_sale=0 WHERE transaction.idrecipt = sales.idreceipt

the problem is that it only updates 5 fields from the transaction help!! what am I doing wrong!!

---

First, I'll point out that it's generally considered bad design to put the same data in two different tables. I'd even argue that there's no reason to store the total_sale anywhere; you can always compute it from the transactions that make up the sale.

The problem you're having is because you're only looking at the current record in Sales. Try this instead. I'm also correcting your command so that it only affects Sales where total_sale=0 now. Otherwise, you're going to set every transaction to 0, which I don't think is what you want:

UPDATE transaction ;
  SET total_sale=0 ;
  FROM sales ;
  WHERE transaction.idreceipt=sales.idreceipt ;
   AND sales.total_sale=0

Tamar

---

The problem is that the pointer is not moving in your Sales table, so you are only working on the same idreceipt. You need to SCAN over the Sales table:

Here's one way to fix that

Select Sales

Scan
    UPDATE transaction SET total_sale=0 WHERE transaction.idrecipt = sales.idreceipt
EndScan