SELECT * FROM REC_DOCUMENT_PELAMAR WHERE REP_ID IN (SELECT PRF_ID FROM temp_master_pcs6) AND KATEGORI = 'FOTO'
SELECT * FROM REC_DOCUMENT_PELAMAR_20220525 WHERE REP_ID IN (SELECT PRF_ID FROM temp_master_pcs6) AND KATEGORI = 'FOTO'

SELECT
	CASE
		WHEN t.[NAMA FILE FOTO] IN ('Ahmad Alexander', 'Ray Airlangga', 'Ria Prastiani', 'Riana Mustika D') THEN '/i/recruitment/PCS6/'+REPLACE(RTRIM(LTRIM(t.[NAMA FILE FOTO])),' ','_')+'.jpeg'
		ELSE '/i/recruitment/PCS6/'+REPLACE(RTRIM(LTRIM(t.[NAMA FILE FOTO])),' ','_')+'.jpg' 
	END AS path_baru,
	*
FROM
	temp_master_pcs6 AS t 
	INNER JOIN
	REC_PELAMAR AS rp ON t.NIP = rp.NIP_BARU
	INNER JOIN
	REC_DOCUMENT_PELAMAR AS d ON rp.REP_ID = d.REP_ID AND d.KATEGORI = 'FOTO'
WHERE
	COALESCE(t.[NAMA FILE FOTO], '') <> ''

UPDATE
	d
SET
	PATH_FILE = CASE
					WHEN t.[NAMA FILE FOTO] IN ('Ahmad Alexander', 'Ray Airlangga', 'Ria Prastiani', 'Riana Mustika D') THEN '/i/recruitment/PCS6/'+REPLACE(RTRIM(LTRIM(t.[NAMA FILE FOTO])),' ','_')+'.jpeg'
					ELSE '/i/recruitment/PCS6/'+REPLACE(RTRIM(LTRIM(t.[NAMA FILE FOTO])),' ','_')+'.jpg' 
				END,
	NAMA = CASE
			WHEN t.[NAMA FILE FOTO] IN ('Ahmad Alexander', 'Ray Airlangga', 'Ria Prastiani', 'Riana Mustika D') THEN REPLACE(RTRIM(LTRIM(t.[NAMA FILE FOTO])),' ','_')+'.jpeg'
			ELSE REPLACE(RTRIM(LTRIM(t.[NAMA FILE FOTO])),' ','_')+'.jpg' 
		END
FROM
	temp_master_pcs6 AS t 
	INNER JOIN
	REC_PELAMAR AS rp ON t.NIP = rp.NIP_BARU
	INNER JOIN
	REC_DOCUMENT_PELAMAR AS d ON rp.REP_ID = d.REP_ID AND d.KATEGORI = 'FOTO'
WHERE
	COALESCE(t.[NAMA FILE FOTO], '') <> ''