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.
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)
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.
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 :(:(
HI ! you can also try this : - SELECT (SELECT COUNT(quantity) FROM t1) - (SELECT COUNT(quantity) FROM t2)
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
- 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 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).
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:(
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.
- 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 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: - 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)
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 |
----------+
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:):)
VanessaMeacham,
Forgot to thank you, thank you so much dude
zmbd 5,501
Expert Mod 4TB
Ahh... I knew I had fogotten something in that SQL!
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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 ((.=.));
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |