468,514 Members | 1,658 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,514 developers. It's quick & easy.

subtracting 2 columns of different tables with a update query

126 64KB
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)

14 19322
zmbd
5,400 Expert Mod 4TB
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
PreethiGowri
126 64KB
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
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
PreethiGowri
126 64KB
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
PreethiGowri
126 64KB
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
5,400 Expert Mod 4TB
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
PreethiGowri
126 64KB
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
12,513 Expert Mod 8TB
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
PreethiGowri
126 64KB
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
5,400 Expert Mod 4TB
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
PreethiGowri
126 64KB
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
PreethiGowri
126 64KB
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
PreethiGowri
126 64KB
VanessaMeacham,
Forgot to thank you, thank you so much dude
Nov 1 '12 #14
zmbd
5,400 Expert Mod 4TB
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.

Similar topics

1 post views Thread by Travis | last post: by
2 posts views Thread by NigelMThomas | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.