Ask Your Question
0

Update statement returns syntax error single value expected

asked 2016-08-15 20:18:53 +0200

koolninja gravatar image

Table "Material" have column "SlNo" "ProductName"

Table "Total" Have column "SlNo" "ProductName" "RecieptTotal" "IssueTotal ""Balance"

SELECT "Material"."ProductName" FROM "Material" group by "Material"."ProductName"

Above sql statement works in Tool SQL but when Update added then gets error .. Single value expected

update "Total" set "Total"."ProductName" = SELECT "Material"."ProductName" FROM "Material" group by

"Material"."ProductName" . can u help me out

Using HSQLDB

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-08-15 20:28:37 +0200

Ratslinger gravatar image

First, it seems you have a lot of questions with SQL. You should take a bit of time to learn/understand it. Here is a site which should help even if only for a reference: click here.

You question has multiple problems. Group By is used when trying to accumulated something. Not to be used here. Next is how to determine which Material.ProductName is to be retrieved. This is done with a Where statement.

Example:

UPDATE "Total" SET "Total"."ProductName" = (SELECT "Material"."ProductName" FROM "Material" WHERE "Total"."ID" = "Material"."ID")

I don't see this defined in your tables, just Auto increment numbers which are not sufficient. There is no correlation.

From previous questions, it appears you are trying to create a new "Total" record based upon a new "Material" record. Is this the case?

edit flag offensive delete link more

Comments

i want to update ProductName of "Total" from "Material" ProductName (as in "Material" Table can have multiple same ProductName with different ID but to update "Total" ProductName with no duplicates. so i using group by..)

koolninja gravatar imagekoolninja ( 2016-08-16 13:59:04 +0200 )edit

One of us is not understanding the other. Are you trying to eliminate duplicates in "Total"? If not, you do not understand my explanation. You cannot update something in one table based upon another table without knowing what record to change. That is what the "WHERE" clause does. Example: Material.ProductName = "A". Which record in the the Total table does it change?

Ratslinger gravatar imageRatslinger ( 2016-08-16 16:17:12 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-08-15 20:18:53 +0200

Seen: 472 times

Last updated: Aug 15 '16