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

Update set command not working in query

Highlighted

Update set command not working in query

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);

7 REPLIES 7
Highlighted

Re: Update set command not working in query

Super Guru

@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

Highlighted

Re: Update set command not working in query

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

Highlighted

Re: Update set command not working in query

Super Guru

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.

Highlighted

Re: Update set command not working in query

@mqureshi : Still not working.

Highlighted

Re: Update set command not working in query

Super Guru

@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.

Re: Update set command not working in query

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

Highlighted

Re: Update set command not working in query

@sanjeevan mahajan

Please disclose the source of your questions recently posted.

Don't have an account?