Updating Table from another table
-
- Posts: 4
- Joined: Wed Dec 08, 2021 7:32 am
Updating Table from another table
I have problem to update records in table from another table using SQL commands. It gives syntax error.
I am using two tables Item and Transaction. I want to update Field StockAvailable in Item table from The Field Quantity from Transaction table. the command is as follows:
UPDATE Item e
INNER JOIN Transaction p
ON e.Item = p.Transaction
SET StockAvailable = (StockAvailable - Quantity) Where TrType = "Sale";
it gives error message "Table not found in statement [Update Item]
kindly resolve it so that i can update the stock available after sale of items.
I am using two tables Item and Transaction. I want to update Field StockAvailable in Item table from The Field Quantity from Transaction table. the command is as follows:
UPDATE Item e
INNER JOIN Transaction p
ON e.Item = p.Transaction
SET StockAvailable = (StockAvailable - Quantity) Where TrType = "Sale";
it gives error message "Table not found in statement [Update Item]
kindly resolve it so that i can update the stock available after sale of items.
OpenOffice 4.1.10 Windows 10
Re: Updating Table from another table
If you are using a HSQL database: http://www.hsqldb.org/doc/1.8/guide/ch0 ... on-section (scroll down to "name").
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Updating Table from another table
You do not need the field names with the table names in the two first lines. The "on" clause should take care of selecting the joining fields.
Re: Updating Table from another table
Code: Select all
UPDATE "Item" "e"
INNER JOIN "Transaction" "p"
ON "e"."Item" = "p"."Transaction"
SET "StockAvailable" = ("StockAvailable" - "Quantity") Where "TrType" = 'Sale';
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Updating Table from another table
@Prashant Kumar
you are not helping others help you. your code is a mess & lacks clarity.
the default for HSQLDB table/field names is upper case.
e.g. Item is converted internally to ITEM.
if 'Item' (camel or mixed case) is the name of your table then it must be surrounded by double quotes i.e. "Item" otherwise use ITEM (uppercase).
field names should be referenced explicitly i cannot tell which tables contain the fields TrType or "Sale".
here is a link to an old post.
viewtopic.php?p=264088#p264088
NOTE: the code is crystal clear but pay attention to the comments of user Ramne in the final post.
you are not helping others help you. your code is a mess & lacks clarity.
the default for HSQLDB table/field names is upper case.
e.g. Item is converted internally to ITEM.
if 'Item' (camel or mixed case) is the name of your table then it must be surrounded by double quotes i.e. "Item" otherwise use ITEM (uppercase).
field names should be referenced explicitly i cannot tell which tables contain the fields TrType or "Sale".
here is a link to an old post.
viewtopic.php?p=264088#p264088
NOTE: the code is crystal clear but pay attention to the comments of user Ramne in the final post.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
-
- Posts: 4
- Joined: Wed Dec 08, 2021 7:32 am
Re: Updating Table from another table
When I used the above code in create query in SQL view I got the following error:Villeroy wrote:Lower case names in double-quotes, literal string in single quotes.Code: Select all
UPDATE "Item" "e" INNER JOIN "Transaction" "p" ON "e"."Item" = "p"."Transaction" SET "StockAvailable" = ("StockAvailable" - "Quantity") Where "TrType" = 'Sale';
SQL Status: HY000
Error code: 1000
syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE
I have created two tables Item and Transaction.
Structure if TABLE Item is
ItemCode BigInt Primary
ItemName Text[Varchar]
ItemDescription Text[Varchar]
Price Decimal
StockAvailable Number
Item Sold Number
SupplierID BigInt
Structure of Table Transaction is
TranID BigInt Primary
ItemCode BigInt
TrDate Date
ProductID BigInt
TranType Text[Varchar]
Quantity Number
Both tables are related on ItemCode.
I want to maintain stock available using above two tables. If tranType is 'Sale' the stock available field is updated when I use the query to maintain stock available in Item Table.
Please suggest me the SQL code for the same. I am using OpenOffice 4.1.11
OpenOffice 4.1.10 Windows 10
-
- Posts: 4
- Joined: Wed Dec 08, 2021 7:32 am
Updating fields of one Table from another table
I have created two tables Item and Transaction.
Structure of TABLE Item is
ItemCode BigInt Primary
ItemName Text[Varchar]
ItemDescription Text[Varchar]
Price Decimal
StockAvailable Number
Item Sold Number
SupplierID BigInt
Structure of Table Transaction is
TranID BigInt Primary
ItemCode BigInt
TrDate Date
ProductID BigInt
TranType Text[Varchar]
Quantity Number
Both tables are related on ItemCode.
I want to maintain stock available using above two tables. If tranType is 'Sale' the stock available field is updated when I use the query to maintain stock available in Item Table.
Please suggest me the SQL code to update StockAvailable for the same. I am using OpenOffice 4.1.11
Structure of TABLE Item is
ItemCode BigInt Primary
ItemName Text[Varchar]
ItemDescription Text[Varchar]
Price Decimal
StockAvailable Number
Item Sold Number
SupplierID BigInt
Structure of Table Transaction is
TranID BigInt Primary
ItemCode BigInt
TrDate Date
ProductID BigInt
TranType Text[Varchar]
Quantity Number
Both tables are related on ItemCode.
I want to maintain stock available using above two tables. If tranType is 'Sale' the stock available field is updated when I use the query to maintain stock available in Item Table.
Please suggest me the SQL code to update StockAvailable for the same. I am using OpenOffice 4.1.11
OpenOffice 4.1.10 Windows 10
Re: Updating Table from another table
Queries are SELECT statements.
menu:Tools>SQL... is for everything else.
menu:Tools>SQL... is for everything else.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Updating Table from another table
@Prashant Kumar
your project is doomed to failure, you are ignoring the basic rules of database normalisation.
i am always reluctant to supply a solution to threads like this because i feel that will only encourage you to stick with what you have.
i provided a link in my previous post which if followed should have enabled you to solve your issue with minimum effort.
this code answers your question, execute via menu:Tools>SQL:
we should never store calculated values!
what you probably require is a running total.
running totals are extremely resource intensive & although we could integrate one within our input form it is not advisable.
i attach a no frills database made in libreoffice using hsqldb embedded which includes a running total.
because we are using an ancient database i had to use two VIEWS created from queries in order to use a running sum within a subform.
your project is doomed to failure, you are ignoring the basic rules of database normalisation.
i am always reluctant to supply a solution to threads like this because i feel that will only encourage you to stick with what you have.
i provided a link in my previous post which if followed should have enabled you to solve your issue with minimum effort.
this code answers your question, execute via menu:Tools>SQL:
Code: Select all
update "Item" i set "StockAvailable" = "StockAvailable" -
(select "Quantity" from "Transaction" where "ItemCode" = i."ItemCode" and "TranType" = 'Sale')
where 1 in
(select 1 from "Transaction" where "ItemCode" = i."ItemCode" and "TranType" = 'Sale')
what you probably require is a running total.
running totals are extremely resource intensive & although we could integrate one within our input form it is not advisable.
i attach a no frills database made in libreoffice using hsqldb embedded which includes a running total.
because we are using an ancient database i had to use two VIEWS created from queries in order to use a running sum within a subform.
- Attachments
-
- RunningSum.odb
- Running Sum
- (15 KiB) Downloaded 353 times
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
-
- Posts: 4
- Joined: Wed Dec 08, 2021 7:32 am
Re: Updating Table from another table
Your Code is
update "Item" i set "StockAvailable" = "StockAvailable" -
(select "Quantity" from "Transaction" where "ItemCode" = i."ItemCode" and "TranType" = 'Sale')
where 1 in
(select 1 from "Transaction" where "ItemCode" = i."ItemCode" and "TranType" = 'Sale')
When I execute the sent code through menu:Tools>SQL, I receive the status
1: Single value expected
the sent code is not working. please resolve this issue.
update "Item" i set "StockAvailable" = "StockAvailable" -
(select "Quantity" from "Transaction" where "ItemCode" = i."ItemCode" and "TranType" = 'Sale')
where 1 in
(select 1 from "Transaction" where "ItemCode" = i."ItemCode" and "TranType" = 'Sale')
When I execute the sent code through menu:Tools>SQL, I receive the status
1: Single value expected
the sent code is not working. please resolve this issue.
OpenOffice 4.1.10 Windows 10
Re: Updating Table from another table
so you have multiple transactions for any given "ItemCode", why was this not made clear?
the only information presented by you (an invalid UPDATE statement & shambolic database structure) appears to indicate single transactions i.e. missing SUM & GROUP BY in the UPDATE statement.
my advice is to scrap what you have, spend some time & energy researching invoice database design & start again from scratch.
if/when you seek help/advice on any forum then at least make the effort to provide all the necessary/relevant/crucial detail required by others in order to deduce a possible solution.
the only information presented by you (an invalid UPDATE statement & shambolic database structure) appears to indicate single transactions i.e. missing SUM & GROUP BY in the UPDATE statement.
my advice is to scrap what you have, spend some time & energy researching invoice database design & start again from scratch.
if/when you seek help/advice on any forum then at least make the effort to provide all the necessary/relevant/crucial detail required by others in order to deduce a possible solution.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10