Update set command not working in query

[#1]

I don't know what I am doing wrong .

UPDATE acid_xyz SET hire_date = (select from_date from acid_abc where acid_xyz.eid = acid_abc.eid);

[#2]

@sanjeevan mahajan

Your select clause doesn't make sense. I cannot be sure but are you trying to do the following?

select from_date from acid_abc inner join acid_xyz on acid_xyz.eid = acid_abc.eid

[#3]

@mqureshi I am trying to update value of hire_date of xyz table with from_date of abc table where the eid matches.

[#4]

Yes I know but where clause in your select query doesn't make sense. Try to run just the select clause and see what you get.

[#5]

@mqureshi : Still not working.

[#6]

@sanjeevan mahajan

Per apache hive documentation subquery for update is not supported

• Syntax
  Standard Syntax:UPDATE tablename SET column = value [, column = value ...] [WHERE expression]
  Synopsis
  • The referenced column must be a column of the table being updated.
  • The value assigned must be an expression that Hive supports in the select clause. Thus arithmetic operators, UDFs, casts, literals, etc. are supported. Subqueries are not supported.
  • Only rows that match the WHERE clause will be updated.
  • Partitioning columns cannot be updated.
  • Bucketing columns cannot be updated.
  • In Hive 0.14, upon successful completion of this operation the changes will be auto-committed.

[#7]

Also note that support for a JOIN clause in the update statement is not available. See this HCC article for more information and a link to the Hive JIRA requesting support for a MERGE statement. https://community.hortonworks.com/questions/11652/how-to-update-hive-row-with-join.html

[#8]

@sanjeevan mahajan

Please disclose the source of your questions recently posted.