0

I'm trying to update some info in the DB because of a wrong use of the last name when there is a prefix.

I have constructed the following:

UPDATE subject_detail_overall sdo
JOIN subject_detail_personal sdp ON sdo.subject_id = sdp.subject_id
SET name_lessformal = 
    IF (sdp.sex = 1) THEN
        sdo.name_lessformal := substr(sdo.name_lessformal,1,7)||upper(substr(sdo.name_lessformal,8,1))||substr(sdo.name_lessformal,9)
    ELSIF (sdp.sex = 2) THEN    
        sdo.name_lessformal := substr(sdo.name_lessformal,1,5)||upper(substr(sdo.name_lessformal,6,1))||substr(sdo.name_lessformal,7)
    END IF
WHERE (sdp.sex = 1 OR sdp.sex = 2) AND 
((sdp.name_birthnameprefix IS NOT NULL AND ((sdp.useofname = 2) OR (sdp.useofname = 3))) 
OR (sdp.name_partnernameprefix IS NOT NULL) AND ((sdp.useofname = 1) OR (sdp.useofname = 4)))

This is to turn the first letter of the prefix to uppercase when there is only a miss or mister in front of it.

useofname is which last name is used and in what order (partnername-birthname, only partnername, ect.)

It seems I can't use join in that place, but I don't know how to reshape the query so it does work.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Basje313
  • 29
  • 5

1 Answers1

0
  • You should probe MERGE INTO statement.
  • SQL work with CASE clause against IF.
  • You sould ignore so much or. It is very performance unfriendly.

Your sql can be like this. But I can't test without tables.

MERGE INTO subject_detail_overall sdo
USING (
SELECT subject_id, CASE WHEN dp.sex = 1
  THEN
substr(sdo.name_lessformal,1,7)||upper(substr(sdo.name_lessformal,8,1))||substr(sdo.name_lessformal,9)
WHEN dp.sex = 2
  THEN 
substr(sdo.name_lessformal,1,5)||upper(substr(sdo.name_lessformal,6,1))||substr(sdo.name_lessformal,7)
END AS result
FROM subject_detail_personal sdp
WHERE (sdp.sex = 1 OR sdp.sex = 2) AND 
((sdp.name_birthnameprefix IS NOT NULL AND ((sdp.useofname = 2) OR (sdp.useofname = 3))) 
OR (sdp.name_partnernameprefix IS NOT NULL) AND ((sdp.useofname = 1) OR (sdp.useofname = 4))) 
) base ON sdo.subject_id = base.subject_id 

WHEN MATCHED THEN UPDATE SET name_lessformal = base.result
László Tóth
  • 483
  • 5
  • 15