SQLTeam.com | Weblogs | Forums

Update table based on like value from another table

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')

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 ?

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.

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

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.

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
1 Like

Amazing! Many many thanks.