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
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?
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…)
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?