Update table based on like value from another table

Transact-SQL
1

I have a table , Trade_Main, with values such as this in it ' GB / Wolv 26th Sep'
I have another table,Trackes, with values such as this in it 'Wolv'
I want to use the value in the latter table to update the values in the first table, so that all values like '%Wolv%' in the first table would be updated to 'Wolv'

CREATE TABLE [dbo].[Trade_Main](
[Trade_Id] [int] IDENTITY(1,1) NOT NULL,
[Menu_Hint] [nvarchar](250) NULL,

CONSTRAINT [PK_Trade_Main] PRIMARY KEY CLUSTERED
(
[Trade_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

insert into Trade_Main(Menu_Hint)
values
('GB / Wolv 26th Sep',
'GB / Chep 2nd Nov',
'GB / Wolv 20th Dec',
'GB / Newc 21st Jan',
'GB / Wind 28th Oct',
'GB / Ham 17th May',
'GB / Newb 16th Sep',
'GB / Towc 17th Dec',
'GB / Hunt 11th Oct',
'GB / Newb 16th Apr',
'GB / Weth 22nd Apr',
'GB / Kemp 27th Sep',
'GB / Newt 18th Jul',
'GB / Ayr 15th Sep',
'GB / Redc 4th Nov')

CREATE TABLE [dbo].[Tracks](
[Tracks_Id] [int] IDENTITY(1,1) NOT NULL,
[Tracks_Short_Name] nvarchar NULL,
CONSTRAINT [PK_Tracks] PRIMARY KEY CLUSTERED
(
[Tracks_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

insert into Tracks(Tracks_Short_Name)
values
('Wolv',
'Chep',
'Newc',
'Wind',
'Ham',
'Newb',
'Towc',
'Hunt',
'Newb',
'Weth',
'Kemp',
'Newt',
'Ayr',
'Redc')

2

doable but question before we recommend a solution. what will happen if tomorrow all the values in Tracks chaneg their values? Will that require an update to Trade_Main values also? Do you see where I am going with this ?

3

The values in Tracks are fixed. All that can happen to Tracks_Short_Name is that values get added or deleted, but existing values don't change.

4

so when it is deleted what happens to the corresponding value in Trade_Main ?

5

It will be deleted, but these are sports stadia, and they rarely change, ie, once built they continue to be used, and few if any are added to the estate.

6 
UPDATE TM
SET Menu_Hint = T.Tracks_Short_Name
FROM dbo.Trade_Main TM
CROSS APPLY (
    SELECT TOP (1) *
    FROM dbo.Tracks T
    WHERE TM.Menu_Hint LIKE N'%' + T.Tracks_Short_Name + N'%'
    ORDER BY LEN(T.Tracks_Short_Name) DESC /*just in case*/
) AS T
2 Likes
7

Amazing! Many many thanks.