Using substring from one field to update another field in the same table

Scriptcase 8 Discussion
, , ,
#1

I need to create description based on the named of the part number. So far I’ve been able to cobble together;

MERGE INTO PART_ROUTING T
USING PART_ROUTING S
ON T.PRTNUM_12 = S.PRTNUM_12
and T.PRTNUM_12 = ‘0110-0936-0011’

WHEN MATCHED THEN
UPDATE
SET T.OPRDES_12 = SUBSTRING(T.PRTNUM_12,6,4);

but I’m getting this error:
Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

Can anyone see my way out of this?

#2

I went a different way. Looks like it does the job. Still testing.

USE GNCMAX5;
GO
DROP TABLE PART_ROUTING_TEMP;
CREATE TABLE PART_ROUTING_TEMP (
PRTNUM_12 NVARCHAR(30),
OPRSEQ_12 CHAR(4),
ALTCDE CHAR(30),
OPRDES_12 CHAR(25));
GO
INSERT INTO PART_ROUTING_TEMP
SELECT PRTNUM_12,OPRSEQ_12, ALTCDE_12,CONCAT(‘CUT RAILS AT ‘,SUBSTRING(PRTNUM_12,6,2),’ IN’)
FROM Part_Routing
/* where prtnum_12 in (‘0110-0936-0011’,’ 0121-1527-0001’);*/

MERGE INTO PART_ROUTING T
USING PART_ROUTING_TEMP S
ON T.PRTNUM_12 = S.PRTNUM_12
and T.OPRSEQ_12 = S.OPRSEQ_12

WHEN MATCHED THEN
UPDATE
SET T.OPRDES_12 = SUBSTRING(T.PRTNUM_12,6,4);
GO
SELECT *
FROM PART_ROUTING_TEMP
GO;