You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Priyanka Raghuvanshi <Pr...@winjit.com> on 2016/10/17 09:14:57 UTC

Update operation on hive table using Join

Hi,

Is there any way to  update a Hive table with join.
Following is the query :

SQL query

UPDATE _STCC_CurrentLocationMaPData
set [Address]=replace(CL.cOut_Event_Description,'''''''',''''),
[Latitude]=CL.fOut_latitude ,
[Longitude]=CL.fOut_longitude ,
[Speed] =CL.fOut_Speed,
[dTimeStamp] =MV.dTimeStamp,
[Reg_No]=CL.cOut_registration,
[GPSState] = CL.bGPS
FROM  #MaxValues MV
INNER JOIN CartrackDetailsInfo CL on MV.dTimeStamp=CL.dTimeStamp AND MV.AcqAccountNumber=CL.AcqAccountNumber
INNER JOIN  _STCC_CurrentLocationMaPData CLR on MV.AcqAccountNumber=CLR.AccountNo
where CLR.AccountNo=MV.AcqAccountNumber


HQL query
UPDATE current_location set address=regexp_replace(cl.cout_event_description,'''''''',''''),latitude=cl.latitude,longitude=cl.longitude ,speed =cl.speed,dtimestamp =mv.dtimestamp,reg_no=cl.registration,gpsstate = cl.bgps
FROM  default.maxvalues mv
INNER JOIN cartrackdetailsinfo cl on mv.dtimestamp=cl.dtimestamp ANDmv.acqaccountnumber=cl.acqaccountnumber
INNER JOIN current_location clr on mv.acqaccountnumber=clr.deal_number
where clr.deal_number=mv.acqaccountnumber;
Output
FAILED: ParseException line 1:221 missing EOF at 'FROM' near 'bgps

Re: Update operation on hive table using Join

Posted by Eugene Koifman <ek...@hortonworks.com>.
This is not supported.  The same can be achieved using Merge statement which is WIP: https://issues.apache.org/jira/browse/HIVE-10924.

Eugene

From: Priyanka Raghuvanshi <Pr...@winjit.com>>
Reply-To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Date: Monday, October 17, 2016 at 2:14 AM
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: Update operation on hive table using Join

Hi,

Is there any way to  update a Hive table with join.
Following is the query :

SQL query

UPDATE _STCC_CurrentLocationMaPData
set [Address]=replace(CL.cOut_Event_Description,'''''''',''''),
[Latitude]=CL.fOut_latitude ,
[Longitude]=CL.fOut_longitude ,
[Speed] =CL.fOut_Speed,
[dTimeStamp] =MV.dTimeStamp,
[Reg_No]=CL.cOut_registration,
[GPSState] = CL.bGPS
FROM  #MaxValues MV
INNER JOIN CartrackDetailsInfo CL on MV.dTimeStamp=CL.dTimeStamp AND MV.AcqAccountNumber=CL.AcqAccountNumber
INNER JOIN  _STCC_CurrentLocationMaPData CLR on MV.AcqAccountNumber=CLR.AccountNo
where CLR.AccountNo=MV.AcqAccountNumber


HQL query
UPDATE current_location set address=regexp_replace(cl.cout_event_description,'''''''',''''),latitude=cl.latitude,longitude=cl.longitude ,speed =cl.speed,dtimestamp =mv.dtimestamp,reg_no=cl.registration,gpsstate = cl.bgps
FROM  default.maxvalues mv
INNER JOIN cartrackdetailsinfo cl on mv.dtimestamp=cl.dtimestamp ANDmv.acqaccountnumber=cl.acqaccountnumber
INNER JOIN current_location clr on mv.acqaccountnumber=clr.deal_number
where clr.deal_number=mv.acqaccountnumber;
Output
FAILED: ParseException line 1:221 missing EOF at 'FROM' near 'bgps