Although this is a complex situation, part of the problem lies within the which record is to be updated?
. Given:
There are three different records in the table where LoaNo = 123 and MaterialName = linen
. Since the Balance for each of these is NULL which is to be updated? Also there are multiple results so the Update
statement becomes confused.
What do expect you end result to be?
Edit: Not knowing what you are expecting the following SQL:
UPDATE "Test"
SET "Balance" = (SELECT COALESCE("a"."TotalRcvd" - "b"."TotalReleased", "a"."TotalRcvd") AS "Balance"
FROM (SELECT "Type",
"LoaNo",
"MaterialName",
SUM("Quantity") AS "TotalRcvd"
FROM "Test"
WHERE "Type" = 'Recieved'
GROUP BY "LoaNo",
"MaterialName",
"Type") "a"
LEFT JOIN (SELECT "Type",
"LoaNo",
"MaterialName",
SUM("Quantity") AS "TotalReleased"
FROM "Test"
WHERE "Type" = 'Released'
GROUP BY "LoaNo",
"MaterialName",
"Type") "b"
ON ("a"."LoaNo" = "b"."LoaNo")
AND ("a"."MaterialName" = "b"."MaterialName")
WHERE ("a"."LoaNo" = "Test"."LoaNo")
AND ("a"."MaterialName" = "Test"."MaterialName"))
WHERE ("Test"."Balance" IS NULL)
Will give the following results based upon the data used in this answer: