try this -- populate table user_parameters with user_id values (unique)from user_details INSERT user_parameters SELECT user_id, null, null FROM user_details
-- Update remaining columnsd UPDATE user_parameters SET param_name = t1.user_name param_value = t1.user_address FROM user_parameters t2 JOIN user_details t1 ON t2.user_id = t1.user_id; Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* http://talebzadehmich.wordpress.com On 23 April 2016 at 07:04, Deepak Khandelwal <dkhandelwal....@gmail.com> wrote: > Hi All, > > I am new to Hive and I am trying to create a query for below aituation. > Would appreciate if someone could guide on same. Thans a lot in advance. > > I have two TABLES shown below > > TABLE1 (USER_dETAILS) > **USER_ID** | **USER_NAME** | **USER_ADDRESS** > ------------+------------------+---------------- > 1 USER1 ADDRESS111 > 2 USER2 ADDRESS222 > > TABLE2 (USER_PARAMETERS) > **USER_ID** | **PARAM_NAME** | **PARAM_VALUE** > ------------+------------------+------------------ > 1 USER_NAME USER1 > 1 USER_ADDRESS ADDRESS111 > 2 USER_NAME USER2 > 2 USER_ADDRESS ADDRESS222 > > I need to insert data in table2(USER_PARAMETERS) FROM table1(USER_DETAILS) > in the format shown above. I can do this using UNION ALL but I want to > avoid it as there are like 10 such columns that i need to split like above. > > Can someone suggest a efficient hive query so that i can achieve the > results shown in table 2 from data in table 1 (Hive query to split one row > of data into multiple rows like such that Row 1 will have column1 Name, > column1 Value and Row 2 will have column 2 Name and column 2 value...). > > Thanks a lot > Deepak > >