Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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?
    Last edited by onyxtacular; 03-26-2016, 01:31 PM. Reason: added tags
    HA! We're all just a little crazy.

  • #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;
    HA! We're all just a little crazy.

    Comment

    Working...
    X