Update Query Failing when using Sub query for where clause

[#1]

Hi All

I am using Hive3.1.5. I am getting below error for the update query where sub-queries are used.

Query:

UPDATE tableA 
SET ColA = "Value" WHERE year(DateColumn)>=(select (max(year(DateColumn))-1) from tableB);

Error:

undefined:-1 Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Any leads on this. How this can be avoided

[#2]

I was able to reproduce this error and it looks like the problem is the identical column name in your tableA and tableB. Namely, DateColumn is referenced in the subquery. Hive interprets this as a reference to the parent query which is not allowed (per limitation listed here). Essentially it's confused what you mean by this query due to overloaded column name.

To solve this, you can explicitly specify table names when referring to columns:

UPDATE tableA 
SET tableA.ColA = "Value" 
WHERE year(tableA.DateColumn) >= (
                                  select (max(year(tableB.DateColumn))-1) 
                                  from tableB
                                  )

Let me know if this works.

Regards,

Alex