SAP HANA Update Table from 2 different Schema

[bogdanbgddm]

schema.png

I am trying to update TABLE2 from SCHEMA2 with Location values from TABLE1 from SCHEMA1 by SN and DATAID (the combination of SN and DATAID must be the same in order to update the location column) I've used the below code:

UPDATE "SCHEMA2"."TABLE2"
SET "LOCATION" = (SELECT LOCATION FROM "SCHEMA1"."TABLE1") 
WHERE "SN" = (SELECT SN FROM "SCHEMA1"."TABLE1") 
AND "DATAID" = (SELECT DATAID FROM "SCHEMA1"."TABLE1") 

i have the following error: "SAP DBTech JDBC: [305]: single-row query returns more than one row" How can I resolve this?

Many thanks!

[RichS]

Have you tried something like this:

UPDATE T2 SET T2."LOCATION" = T1."LOCATION" FROM "SCHEMA2"."TABLE2" T2 JOIN "SCHEMA1"."TABLE1" T1 ON (T1."SN" = T2."SN" AND T1."DATAID" = T2."DATAID")

[dvankempen]

Hi Buganu,

You need to a condition (WHERE) that results in a single value for your subqueries. At least one is now returning multiple values and hence the engine returns an error.

UPDATE "SCHEMA2"."TABLE2"
SET "LOCATION" = (SELECT LOCATION FROM "SCHEMA1"."TABLE1" -- this needs to return one row) 
WHERE "SN" = (SELECT SN FROM "SCHEMA1"."TABLE1" -- this needs to return one row) 
AND "DATAID" = (SELECT DATAID FROM "SCHEMA1"."TABLE1" -- this needs to return one row) 

[bogdanbgddm]

How do I make those rows to return only one value ( the corect one) and fill it in into the TABLE 2 ?

[dvankempen]

That's why we have the WHERE clause (AND, AND, AND) until the query always returns a single row. This is important because the query will still return an error for

SET LOCATION = <null> WHERE SN = <nul>> AND DATAID = <null>