Updating Table from another table

[Prashant Kumar]

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.

[Villeroy]

If you are using a HSQL database: http://www.hsqldb.org/doc/1.8/guide/ch0 ... on-section (scroll down to "name").

[keme]

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.

[Villeroy]

Code: Select all

UPDATE "Item" "e"
INNER JOIN "Transaction" "p"
ON "e"."Item" = "p"."Transaction"
SET "StockAvailable" = ("StockAvailable" - "Quantity") Where "TrType" = 'Sale';

Lower case names in double-quotes, literal string in single quotes.

[chrisb]

@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.

[Prashant Kumar]

Code: Select all

UPDATE "Item" "e"
INNER JOIN "Transaction" "p"
ON "e"."Item" = "p"."Transaction"
SET "StockAvailable" = ("StockAvailable" - "Quantity") Where "TrType" = 'Sale';

Lower case names in double-quotes, literal string in single quotes.

When I used the above code in create query in SQL view I got the following error:

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

[Prashant Kumar]

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

[Villeroy]

Queries are SELECT statements.
menu:Tools>SQL... is for everything else.

[chrisb]

@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:

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')

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.

[Prashant Kumar]

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.

[chrisb]

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.