update all values from different table that has same id

Question

I have two tables named sales and rsales

I have this ff value for table sales

id | pcode | total | discount |
2  |  33   |  100  |   20     |
3  |  33   |  100  |   20     |

and i have this ff value for table rsales

id | pcode | total | discount | sales_id |
4  |  33   |  100  |   20     |    1     |
5  |  33   |  100  |   20     |    2     |
6  |  33   |  100  |   20     |    3     |

My problem is that when I update all values from table sales, the table rsales must be update either if sales.id is equal to sales_id.
so for example if I have updated table sales with id = 2 and 3, the sales_id 2 and 3 from rsales must be updated either.
take note : only 2 and 3 because only that ids is found in table sales ids.

I have this ff codes so far to update table rsales. but the output is shown not as what I meant. it update all values.

mysql_query("UPDATE sales AS t1, rsales AS t2
    SET t1.total = '$total_discount',
    t2.total = '$total_discount',
    t2.discount = '$tot'
    WHERE t1.pcode = '$pcode'");

Obligatory: [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) and [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — , Aug 31 '13 at 18:30
if $pcode has the value 33 and your column pcode only has values with 33 then your database is correct... — Raymond Nijland, Aug 31 '13 at 18:33
Check his history-this guy will ask the same question over and over with slight variations. — Mihai, Aug 31 '13 at 18:34
no, i tried adding WHERE t1.pcode = '$pcode' AND t2.pcode = t1.pcode but it update only sales_id 2 from tbl rsales 3 must be incuded to update — user2656724, Aug 31 '13 at 18:35
sorry sir am just really new to php and getting familiar the inner join my question befor is slightly different from the old one. try to check and read it carefully — user2656724, Aug 31 '13 at 18:37

score 0 · Accepted Answer · answered Aug 31 '13 at 18:37

You should add to the WHERE class the common column between the tables.

UPDATE sales AS t1, rsales AS t2
  SET
    t1.total = '$total_discount',
    t2.total = '$total_discount',
    t2.discount = '$tot'
WHERE
    t1.id = t2.sales_id AND
    t1.pcode = '$pcode'

Note: I haven't tested it.

update all values from different table that has same id

1 Answers1