CODEWITHSUNDEEP
javams-accessjdbchsqldbucanaccess
Sri Kiran
Sri Kiran

Reputation: 21

UPDATE with LEFT JOIN not working with UCanAccess

I am trying to update the table row in MS Access from NetBeans. But I am getting the error -

net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: LEFT required: SET

I had tested the query directly in MS-Access, which is working perfectly. But when I use that same query in NetBeans it throwing the error.

  1. Two tables are connected each other - category and Product tables.
  2. Category ID (Primary Key, AutoIncrement) will be the foreign key in product table.
  3. Now I want to update the details of product in Product table.

My Update Query :

ps_ins_new_prod = con.prepareStatement("UPDATE Inv_Category LEFT JOIN Inv_Product ON Inv_Category.Category_ID = Inv_Product.Category_ID SET Inv_Product.[Size] = ?, Inv_Product.Quantity = ?, Inv_Product.Item_No = ?, Inv_Product.Purchase_Price = ?, Inv_Product.Selling_Price = ?, Inv_Product.Category_ID = ? WHERE (((Inv_Product.Product_Name)=?) AND ((Inv_Product.Size)=?) AND ((Inv_Category.Category_Name)=?) AND ((Inv_Product.Quantity)=?) AND ((Inv_Product.Item_No)=?) AND ((Inv_Product.Purchase_Price)=?) AND ((Inv_Product.Selling_Price)=?) AND ((Inv_Product.Category_ID)=?))");

Upvotes: 2

Views: 601

Answers (1)

wero
wero

Reputation: 32980

Internally Ucanaccess runs your query against a HSQLDB database which does not support LEFT OUTER JOINS in an UPDATE.

See this thread for a discussion and a possible workaround using the MERGE command.

Upvotes: 1

Related Questions