1

I have a table with multiple columns. Now I want to add another column which should have values like "CTO1101"+row_id row_id is the existing column in table.

using below 2 commands is giving error.

alter table d_study add columns(cto_id string);

update d_study
    set cto_id = "CTO1101" + row_id;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Vaibhav
  • 107
  • 1
  • 11
  • Not going to happen. Hive UPDATE currently supports only hard-coded values. You can generate a view over your table,create a new table or overwrite your current table after you have added the additional column. – David דודו Markovitz Aug 03 '17 at 14:06
  • @DuduMarkovitz hey, your approach worked for me. Thanks. – Vaibhav Aug 03 '17 at 14:47
  • @vaibhav It's a duplicate question - https://stackoverflow.com/questions/45438453/creating-and-updating-a-new-column-in-hive – Prem Aug 03 '17 at 16:47
  • @Prem yeah, but was just trying to found the answer. got better suggestion here. could you pls help answer this https://stackoverflow.com/questions/45491829/updating-unique-id-column-for-newly-added-records-in-table-in-hive – Vaibhav Aug 04 '17 at 04:12

1 Answers1

-1

alter table d_study add column cto_id string;
update d_study set cto_id="CTO1101"||row_id;

The syntax depends on the DB system as well as the data types so for Hive just search for the concatenation opperator("||" or a predefined function )

Alexandru
  • 14
  • 2
  • getting following error while updating : ParseException line 1:38 missing EOF at '|' near '"CTO1101"' – Vaibhav Aug 03 '17 at 13:42
  • update d_study set cto_id=concat('CTO1101',row_id); Try the concatenation function then. – Alexandru Aug 03 '17 at 13:45
  • yes tried that also. got this error - Attempt to do update or delete using transaction manager that does not support these operations. I tried for both concat("CTO1101"+row_id) and concat("CTO1101"+str(row_id)) – Vaibhav Aug 03 '17 at 13:48
  • Then it seems that the problem is now not a syntax problem :). Please see [link](https://stackoverflow.com/questions/34198339/attempt-to-do-update-or-delete-using-transaction-manager-that-does-not-support-t) - – Alexandru Aug 03 '17 at 13:53
  • thanks, can you pls help answer this as well https://stackoverflow.com/questions/45491829/updating-unique-id-column-for-newly-added-records-in-table-in-hive – Vaibhav Aug 04 '17 at 04:13