Support Questions
Find answers, ask questions, and share your expertise

Update Query Failing when using Sub query for where clause

New Contributor

Hi All

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



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



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


Super Collaborator

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.



; ;