Update statement from SQL Server to Oracle

Question

SQL Server Syntax:

UPDATE #RELATIONSHIP
SET SEGMENT_START_DT = #SEGMENTS_LANE.SEGMENT_START_DT,
SEGMENT_END_DT = #SEGMENTS_LANE.SEGMENT_END_DT,
SEGMENT_ID = 'XCSLANE' +'-'+ #SEGMENTS_LANE.LANE_ID +'-'+ 
CONVERT(VARCHAR,#SEGMENTS_LANE.SEGMENT_START_DT,113)
FROM #SEGMENTS_LANE
WHERE #RELATIONSHIP.LANE_ID = #SEGMENTS_LANE.LANE_ID
AND #RELATIONSHIP.TO_WE_DT > #SEGMENTS_LANE.SEGMENT_START_DT
AND #RELATIONSHIP.TO_WE_DT <= #SEGMENTS_LANE.SEGMENT_END_DT
AND #SEGMENTS_LANE.SEGMENT_START_DT IS NOT NULL

I converted it to Oracle Syntax:

UPDATE RELATIONSHIP
SET (SEGMENT_START_DT,SEGMENT_END_DT,SEGMENT_ID) =
( SELECT SEGMENTS_LANE.SEGMENT_START_DT, SEGMENTS_LANE.SEGMENT_END_DT,
CONCAT(CONCAT(CONCAT(CONCAT( 'XCSLANE','-'),SEGMENTS_LANE.LANE_ID),'-
'),TO_CHAR(SEGMENTS_LANE.SEGMENT_START_DT,'dd mon yyyy hh24:mm:ss'))
FROM SEGMENTS_LANE
WHERE RELATIONSHIP.LANE_ID = SEGMENTS_LANE.LANE_ID
AND RELATIONSHIP.TO_WE_DT > SEGMENTS_LANE.SEGMENT_START_DT
AND RELATIONSHIP.TO_WE_DT <= SEGMENTS_LANE.SEGMENT_END_DT
AND SEGMENTS_LANE.SEGMENT_START_DT IS NOT NULL)
WHERE RELATIONSHIP.lane_id IN ( SELECT SEGMENTS_LANE.LANE_ID FROM SEGMENTS_LANE)

Is this correct? I suspect that I am changing the logic of the update.

Answer 1

I think the logic is the same, although I don't know SQL Server. I can tell you that the Oracle equivalent does this:

1) Update all RELATIONSHIP rows where there is a corresponding SEGMENTS_LANE row with the same LANE_ID

2) Set the RELATIONSHIP row's (SEGMENT_START_DT,SEGMENT_END_DT,SEGMENT_ID) to the value returned by:

SELECT SEGMENTS_LANE.SEGMENT_START_DT, SEGMENTS_LANE.SEGMENT_END_DT,
CONCAT(CONCAT(CONCAT(CONCAT( 'XCSLANE','-'),SEGMENTS_LANE.LANE_ID),'-
'),TO_CHAR(SEGMENTS_LANE.SEGMENT_START_DT,'dd mon yyyy hh24:mm:ss'))

FROM SEGMENTS_LANE
WHERE RELATIONSHIP.LANE_ID = SEGMENTS_LANE.LANE_ID
AND RELATIONSHIP.TO_WE_DT > SEGMENTS_LANE.SEGMENT_START_DT
AND RELATIONSHIP.TO_WE_DT <= SEGMENTS_LANE.SEGMENT_END_DT
AND SEGMENTS_LANE.SEGMENT_START_DT IS NOT NULL

NB This SELECT must return one row for all the RELATIONSHIP rows to be updated.

Instead of using the CONCAT function, you could use Oracle's || operator:

'XCSLANE' || '-' || SEGMENTS_LANE.LANE_ID || '-' 
|| TO_CHAR(SEGMENTS_LANE.SEGMENT_START_DT,'dd mon yyyy hh24:mm:ss')