TSQL Case WHEN LIKE REPLACE

Question

Newbie question... looking for the fastest way to update a new column based on the existence of a value from another table, while replacing values.

Example, below, taking the words 'Bought a car' with 'car' into another table. The problem is 'Bought a car' is into another table.

I did a hack to reselect the value and do a replace, but with more rows, the performance is horrible, taking up to 3 to 5 minutes to perform.

Oh SQL Gurus, what is the best way to do this?

Example

            DECLARE @Staging_Table TABLE
        (
         ACCTID INT IDENTITY(1,1),
         NAME VARCHAR(50),
         PURCHASES VARCHAR(255)
         )

        INSERT INTO @Staging_Table (Name, Purchases)
        VALUES ('John','Bought a table')
        INSERT INTO @Staging_Table (Name, Purchases)
        VALUES ('Jack','Sold a car')
        INSERT INTO @Staging_Table (Name, Purchases)
        VALUES ('Mary','Returned a chair')

        DECLARE @HISTORY TABLE
        (
         ACCTID INT IDENTITY(1,1),
         NAME VARCHAR(50),
         Item VARCHAR(255)
         )

        INSERT INTO @HISTORY (Name, Item)
        VALUES ('John','')
        INSERT INTO @HISTORY (Name, Item)
        VALUES ('Jack','')
        INSERT INTO @HISTORY (Name, Item)
        VALUES ('Mary','')

        UPDATE @HISTORY
        Set ITEM = CASE WHEN EXISTS(
                Select ts.Purchases as Output from @Staging_Table ts 
                where ts.NAME = Name AND ts.PURCHASES LIKE '%table%') 
                THEN REPLACE((Select ts2.PURCHASES Output 
                       from @Staging_Table ts2 where ts2.NAME = Name AND ts2.PURCHASES LIKE '%table%'),'Bought a ','') 
        WHEN EXISTS(
                Select ts.Purchases as Output from @Staging_Table ts 
                where ts.NAME = Name AND ts.PURCHASES LIKE '%car%') 
                THEN REPLACE((Select ts2.PURCHASES Output 
                       from @Staging_Table ts2 where ts2.NAME = Name AND ts2.PURCHASES LIKE '%car%'),'Bought a ','') 
        End

        SELECT * FROM @HISTORY

Answer 1

DECLARE @Staging_Table TABLE
    (
      ACCTID INT IDENTITY(1, 1) ,
      NAME VARCHAR(50) ,
      PURCHASES VARCHAR(255)
    )

INSERT  INTO @Staging_Table
        ( Name, Purchases )
VALUES  ( 'John', 'Bought a table' ),
        ( 'Jack', 'Sold a car' ),
        ( 'Mary', 'Returned a chair' )

DECLARE @HISTORY TABLE
    (
      ACCTID INT IDENTITY(1, 1) ,
      NAME VARCHAR(50) ,
      Item VARCHAR(255)
    )
INSERT  INTO @HISTORY
        ( Name, Item )
VALUES  ( 'John', '' ),
        ( 'Jack', '' ),
        ( 'Mary', '' )

UPDATE  L
SET     L.ITEM = ( CASE WHEN R.PURCHASES LIKE '%table%'
                        THEN REPLACE(R.PURCHASES, 'Bought a ', '')
                        WHEN R.PURCHASES LIKE '%car%'
                        THEN REPLACE(R.PURCHASES, 'Sold a ', '')
                   END )
FROM    @HISTORY AS L
        JOIN @Staging_Table AS R ON L.NAME = R.NAME
WHERE   ( R.PURCHASES LIKE '%table%'
          OR R.PURCHASES LIKE '%car%'
        )

SELECT  *
FROM    @HISTORY