?
Solved

SQL UPDATE Problem

Posted on 2012-08-27
5
Medium Priority
?
333 Views
Last Modified: 2012-08-31
Hi:  

We have a SQL database of 'parts' and 'models' which those parts fit.  The only link from each model to all of its parts is in text fields.  We use the following query to create a string which identifies all the models each part fits.

The problem is that it only works for one part at a time as defined in the WHERE clause.

We would like to:

1. Expand the query so that it would update the Part.Description text field and append the string,

AND / OR

2. Expand the query so that it would update the Part.Description text field for multiple parts at the same time.

Thank You!
 

DECLARE @HRefList VARCHAR(max)
SELECT @HRefList = COALESCE(@HRefList,' ') + HREF
FROM
(SELECT
CAST(Categories.CategoryID AS varchar(5)) +
CAST(Categories.CategoryName AS varchar(100)) AS HREF
FROM Categories
WHERE Categories.Custom_Where_Clause LIKE '%2031083%') AS HRefTable
SELECT @HRefList AS FitsModels

(The complete query is a little longer as it concatenates the text necessary to produce a list of hyperlinks which appear in the part description so that a customer can click through to any model which a part fits directly from the description of the part).
0
Comment
Question by:VPWcom
  • 2
  • 2
5 Comments
 
LVL 8

Accepted Solution

by:
stalhw earned 1000 total points
ID: 38337564
For your sake I hope all you part numbers are 6 digits long....
Because LIKE '%2031083%'  will also match 12031083, 92031083, etc...
And is there a delimiter between the numbers?

I'm not sure I understand exactly what you are trying to accomplish, but if you control the Database, then your best option would probably be to first create a new link table, that will be 2 simple fields: the primary key for the model, and the primary key for the part.

you don't even need to do this from the software, you can build a ON INSERT trigger on the model table that would populate automatically  that link table.

From there it would be a lot easier (and a lot more efficient) to create a list of the parts for each model.
And it would also allow you to do a function that would return the list of all models for a part, when you need to display that.
0
 
LVL 13

Assisted Solution

by:Jesus Rodriguez
Jesus Rodriguez earned 1000 total points
ID: 38337674
Create a cursor for the parts and then update the description according models the description. Will be a long process because the structure of your tables
More or less THE IDEA WILL BE something like this (You have to create your own querys)


DECLARE TBLTOUPDATE CURSOR FOR
    SELECT MODEL,DESCRIPTION
    FROM MODEL

OPEN CURSOR
FETCH FROM TBLTOUPDATE INTO @MODEL,@DESCRIPTION_THAT_YOU_WANT
BEGIN
    UPDATE PARTS
    SET DESCRIPTION=@DESCRIPTION_THAT_YOU_WANT
    WHERE PARTS.DESCRIP LIKE ''%'+@MODEL +'%''
FETCH NEXT FROM TBLTOUPDATE INTO @MODEL,@DESCRIPTION_THAT_YOU_WANT
END
CLOSE TBLTOUPDATE
0
 
LVL 8

Expert Comment

by:stalhw
ID: 38337788
PS: Concatening string may look like a good idea, but if your description field allows 255 caracters and you concatenate 20 model, I hope your model are very short, because you risk going over the allowed length of your field...

It would be better to look at what your real needs are, then look at the differents ways to get what you need.
And only then implement the solution you choose.
I really doubt you need to put model in parts description field, you'll just create more problems when you'll need to do new stuff.
I really think the way to get all your current needs, and future needs, is to have a separate table that link models to parts, and from there it will be easy do what you need to do

Assuming it is to do things like list with hyperlinks the models that can use a specific part, or the opposite list links to parts for a model.
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 38337823
I'm agree with stalhw. I think that is time that you to purify your database becasue you can run into a bigger problem in hte future. I give you an option but your final point will be doing this because you can mess up your whole database and updating sometimes description that doesn't match with the models.
0
 

Author Closing Comment

by:VPWcom
ID: 38355882
Thank you for the feedback.  I should have added to the question that we do not control the database, so we are - by definition - seeking a poor solution from a design point of view.  That said, I think the CURSOR idea would probably be a means to achieve the outcome, but I am already well beyond the limit of my SQL training or experience, so am nervous about venturing too far from either SELECT or simple UPDATE scenarios.  Since we only have query access through a web interface I am not sure if adding or dropping tables or cursors or stored procedures are allowed or possible.  Since this was a one time effort, I was able to extract all the CategoryID's (models) and Custom_Where_Clause (list of parts) fields, reduce the C_W_C to a comma separated list and pivot the data in Excel to produce a list of models for each part!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

807 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