473,322 Members | 1,911 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 20365
zmbd
5,501 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,501 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,516 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,501 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,501 Expert Mod 4TB
Ahh... I knew I had fogotten something in that SQL!
Nov 1 '12 #15

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

Similar topics

4
by: rdraider | last post by:
I am looking for some assistance with an update query that needs to link 3 tables: This query ran and reported over 230,000 records affected but did not change the field I wanted changed, not...
1
by: Travis | last post by:
I am in the process of building an application that lists several orders on one table with various info to some other tables with other info relating to these orders and access this via .asp. I...
2
by: JMCN | last post by:
hi i need some advice on whether if it would be better to use an append query or an update query. here is the situation, i have linked another database table to my current database. then i...
3
by: Megan | last post by:
hi everybody- it seems like my update query should work. the sql view seems logical. but that might be up to discussion...lol...i'm a newbie! UPDATE , Issue SET .IssueID = . WHERE ((.=.));
2
by: NigelMThomas | last post by:
I have an especially challenging problem. I know there are a few geniuses in this group so perhaps; you can advise me whether or not this can be done as an update query in Access. Thanks. I am...
4
by: satish | last post by:
Values of two columns in two different tables--presentation using select Hi Everyone, i have two tables in the database . One is called address table and one is adressPhone Table. Below...
1
by: empiresolutions | last post by:
Hello Fellow Programmers, I have an issue with a very large PHP page that calls over 20 different MySql Statements. This page is loading somewhat slow and i want to speed things up. I have looked...
0
by: Jerms | last post by:
Hello all, I've been using this site quite a bit since starting my project and have found it very helpful. I have run into a roadblock though that I cant seem to scrounge up a solution to. I...
2
by: jrsonner | last post by:
I have multiple entries in one table that I need to update the entries in the current production table with, to revert that data back before a migration so I can migrate that data again. The...
6
HaLo2FrEeEk
by: HaLo2FrEeEk | last post by:
I have two different tables which havea different number of columns. One of the tables gets a new row every day and is populated with a st of 4 IDs from another table, along with today's date and an...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.