By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,996 Members | 896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,996 IT Pros & Developers. It's quick & easy.

subtracting 2 columns of different tables with a update query

100+
P: 126
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.
Oct 30 '12 #1

✓ answered by zmbd

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:
Expand|Select|Wrap|Line Numbers
  1. UPDATE Main 
  2. LEFT JOIN Sub ON main.name = sub.name
  3. 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)

Share this Question
Share on Google+
14 Replies


zmbd
Expert Mod 5K+
P: 5,388
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.
Oct 30 '12 #2

100+
P: 126
i tried doing so,
Expand|Select|Wrap|Line Numbers
  1. SELECT main.quantity, sub.quantity
  2. FROM main
  3. LEFT JOIN sub
  4. ON main.name=sub.name;
Expand|Select|Wrap|Line Numbers
  1. update main set quantity = (quantity - quantity) where name = pen;
but this isn't working :(:(
Oct 30 '12 #3

P: 31
HI ! you can also try this :
Expand|Select|Wrap|Line Numbers
  1. SELECT (SELECT COUNT(quantity) FROM t1) - (SELECT COUNT(quantity) FROM t2)
Oct 30 '12 #4

100+
P: 126
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
Oct 30 '12 #5

100+
P: 126
Expand|Select|Wrap|Line Numbers
  1. 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?
Oct 30 '12 #6

zmbd
Expert Mod 5K+
P: 5,388
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).
Oct 30 '12 #7

100+
P: 126
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:(
Oct 30 '12 #8

Rabbit
Expert Mod 10K+
P: 12,350
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.
Oct 30 '12 #9

100+
P: 126
Expand|Select|Wrap|Line Numbers
  1. update main 
  2.    set quantity = (quantity - quantity) 
  3.    WHERE 
  4.       (SELECT 
  5.          main.quantity, 
  6.          sub.quantity
  7.       FROM main
  8.          LEFT JOIN sub
  9.             ON main.name=sub.name);
i tried this but it shows me an error saying- operand should contain 1 column(s):(
Oct 31 '12 #10

zmbd
Expert Mod 5K+
P: 5,388
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:
Expand|Select|Wrap|Line Numbers
  1. UPDATE Main 
  2. LEFT JOIN Sub ON main.name = sub.name
  3. 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)
Oct 31 '12 #11

100+
P: 126
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 |
----------+
Nov 1 '12 #12

100+
P: 126
I solved it :) here is the complete code that works perfectly:)
Expand|Select|Wrap|Line Numbers
  1. UPDATE Main 
  2. LEFT JOIN Sub ON main.name = sub.name
  3. SET main.quantity = (main.quantity - sub.quantity) 
  4. WHERE main.name = pen;
Thank you rabbit
Thank you Zmbd
Thanks a ton for your support:):)
Nov 1 '12 #13

100+
P: 126
VanessaMeacham,
Forgot to thank you, thank you so much dude
Nov 1 '12 #14

zmbd
Expert Mod 5K+
P: 5,388
Ahh... I knew I had fogotten something in that SQL!
Nov 1 '12 #15

Post your reply

Sign in to post your reply or Sign up for a free account.