Solved

T-SQL How to look for part of a datafield in a subquery

Posted on 2008-10-26
3
199 Views
Last Modified: 2010-04-21
I have a question regarding how to build a t-sql query.

I want to get a list of Products that is part of a given brand.

The problem is that I have two tables named 'Products' and 'Brands'. The Products table contain among others the fields 'Name' and 'BrandId'. The Brand table has the fields 'BrandId', 'BrandName', 'LanguageID'. The last field is there because the brands are written in four different languages.
Now for some reson the BrandId foreign key has never been implemented, so all the BrandId fields are 0. However the Products.Name field often will have the Brand name in the text, like "Ralph Lauren Glamourous Daylight Eau De Toilette Spray 50ml/1.7oz".

It's no big deal to search for a given brand name, like:
SELECT * FROM [Product] as P
where P.Name like '%Hugo Boss%'

But I was thinking if there was a way to build a query that finds the Brand relating to a given product and then updates the BrandId reference?
To do this manually would require a lot of hours because the product table contains thousands of lines.
0
Comment
Question by:ThomasCookScandinavia
3 Comments
 
LVL 5

Accepted Solution

by:
PaulKeating earned 125 total points
ID: 22806808
I don't have your tables so I can't check it properly, but give this a try:

update Products
set BrandId = (select max(b.BrandId) from Brand b where charindex(b.BrandName,Products.Name) > 0)
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22807205
update products
set brandid = b.brandid
from Brand b
where products.brandname = b.brandname
and b. LanguageID = 'eng'

0
 

Author Closing Comment

by:ThomasCookScandinavia
ID: 31510066
This is great. Thanks a lot.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question