subtracting 2 columns of different tables with a update query

[PreethiGowri]

I want to subtract 2 column of two different tables and update in one of the table say,
i have two tables 'main' and 'sub' both tables have a column named 'quantity' so i have to do it in this way:

update main set quantity = ((quantity of main) - (quantity of sub)) where name = pens;

Somebody help me please.

[zmbd]

You've solved it right there!

Build your query linking the two tables on the "name" field (which btw is a reserved word... you should change that) now create a calculated field between the two quantity fields.

[PreethiGowri]

i tried doing so,

SELECT main.quantity, sub.quantity
FROM main
LEFT JOIN sub
ON main.name=sub.name;

update main set quantity = (quantity - quantity) where name = pen;

but this isn't working :(:(

[VanessaMeacham]

HI ! you can also try this :

SELECT (SELECT COUNT(quantity) FROM t1) - (SELECT COUNT(quantity) FROM t2)

[PreethiGowri]

i don't want to subtract the count of quantity columns but i want to subtract contents of it,
example -table1 main
quantity = 5, name = pen
table2 sub
quantity = 2, name = pen

query something like update main set quantity = (main.quantity - sub.quantity) where name = pen;
to which the answer seem to be

table main
quantity = 3, name = pen

[PreethiGowri]

SELECT ( SELECT quantity FROM main where name = 'pen') - ( SELECT quantity FROM sub where name = 'pen' )

This query works fine, but is there any way through which i can update this value into main table, in a row where name = pen?

[zmbd]

PreethiGowri:
Normally one does not store the result of a calculation unless needed for historical or other such reasons (say a booked room at a special discount booked in the far future).

[PreethiGowri]

I agree, but in my case, I need the data to stored, as i'm working on a database regarding shopping,
as and when the customer purchases some product i should update about the count of how many more are left over:(

[Rabbit]

You actually don't have to store it because you can always calculate the inventory by summing the incoming and subtracting the outgoing. But it can be done both ways. Usually, if you go that route, you would also store the incoming and outgoing in another table so you still have that historical data so you can reconcile the point in time table.

But I digress. You've separated your select and update statements, they need to be combined. You also need to handle your nulls since you're using an outer join.

[PreethiGowri]

update main 
   set quantity = (quantity - quantity) 
   WHERE 
      (SELECT 
         main.quantity, 
         sub.quantity
      FROM main
         LEFT JOIN sub
            ON main.name=sub.name);

i tried this but it shows me an error saying- operand should contain 1 column(s):(

[zmbd]

MySQL has an unusal Update

I think MySQL does something wierd in this case.

I'm reading thru a PDF file I have that is for a fairly old version of MySQL so the following is most likely not correct; however, if I understand what I'm reading (and I'm not sure that I am) then give this a whirl:

UPDATE Main 
LEFT JOIN Sub ON main.name = sub.name
SET main.quantity = (quantity - quantity);

(edit) Oh... I don't have the nulls handled as Rabbit suggested; however, I thought start simple as I'm not sure this works :)(/edit)

[PreethiGowri]

this query is superb:) but i have a problem with this:(
it sets the rest of quantity cells as null, as follow
----------+
quantity |
----------+
NULL |
NULL |
NULL |
NULL |
NULL |
10 |
----------+

[PreethiGowri]

I solved it :) here is the complete code that works perfectly:)

UPDATE Main 
LEFT JOIN Sub ON main.name = sub.name
SET main.quantity = (main.quantity - sub.quantity) 
WHERE main.name = pen;

Thank you rabbit
Thank you Zmbd
Thanks a ton for your support:):)

[PreethiGowri]

VanessaMeacham,
Forgot to thank you, thank you so much dude

[zmbd]

Ahh... I knew I had fogotten something in that SQL!