August 10, 2010 at 2:48 pm
Hello,
I want to follow up on a question I posted a few days ago (under SQL Server 2008), but for some reason I can't find that thread so that I can do my follow up. I assumed threads get removed after a certain time if the inquirer doesn't show up. Now since I'm using the archaic SQL Server 2000, I would like to post my question here and I hope I can get some help.
I would like to update information in table 2 (called D_DESCRIPTION) with information from table 1 (called CONCEPTS). For each row (a concept) in CONCEPTS there must be 2 descriptions in D_DESCRIPTIONS. I putted below some code to help create the tables and insert some data. The problem I'm facing is how to tell SQL server to pick specific columns from 1 concept and update 2 descriptions. In the example below, the expected result for the following 2 rows in the D_DESCRIPTIONS table needs to be as follows:
'54545','5','70009','Term5','533738856105'
'98545','5','70009','Term55','533738856105'
-- First table (concepts)
CREATE TABLE CONCEPTS
(
ConceptID VARCHAR (18) PRIMARY KEY
RequestID INT
FormID INT
MetaTerm VARCHAR(255)
)
-- Inserts
SET IDENTITY_INSERT CONCEPTS ON
INSERT INTO CONCEPTS
(ConceptID, RequestID, FormID, MetaTerm)
SELECT '233434456105','2','51709','Term1' UNION ALL
SELECT '473435456106','4','51709','Term2' UNION ALL
SELECT '283443456102','3','51709','Term3' UNION ALL
SELECT '433435556108','1','51709','Term4' UNION ALL
SELECT '533738856105','5','70009','Term5' UNION ALL
SELECT '923434456101','6','70009','Term6' UNION ALL
-- Second table (D_DESCRIPTION)
CREATE TABLE D_DESCRIPTION
(
IdDescription INT IDENTITY(1,1) PRIMARY KEY
RequestID INT
FormID INT
DescriptionID VARCHAR(18)
ConceptID VARCHAR(18)
Term VARCHAR(255)
)
-- Inserts
SET IDENTITY_INSERT D_DESCRIPTION ON
INSERT INTO D_DESCRIPTION
(Id_D_Description, RequestID, FormID, DescriptionID, ConceptID, Term)
SELECT '44345','2','51709','233434456118','233434456105', 'Term1 (category)' UNION ALL
SELECT '29675','2','51709','233434456113','233434456105', 'Term11' UNION ALL
SELECT '29341','4','51709','223434456116','473435456106', 'Term2 (category)' UNION ALL
SELECT '34548','4','51709','223434456111','473435456106', 'Term22' UNION ALL
SELECT '12357','3','51709','233733452119','283443456102', 'Term3 (category)' UNION ALL
SELECT '98457','3','51709','333633452113','283443456102', 'Term33' UNION ALL
SELECT '23545','1','51709','733123456111','433435556108', 'Term4 (category)' UNION ALL
SELECT '23643','1','51709','533123456111','433435556108', 'Term44' UNION ALL
SELECT '54545','','','','533738856105', '' UNION ALL
SELECT '98545','','','','533738856105', '' UNION ALL
SELECT '69446','','','','923434456101', '' UNION ALL
SELECT '39541','','','','923434456101', '' UNION ALL
SET IDENTITY_INSERT D_DESCRIPTION OFF
Your help is much appreciated.
Z
August 10, 2010 at 3:42 pm
Even though I have no idea what value you want to store in the DescriptionID column nor do I have a clue as how you'd calculate the number to be used in the Term column nor were your data ready to use... here's what I came up with:
;
WITH cte AS
(
SELECT
d.*,
ROW_NUMBER() OVER(PARTITION BY d.conceptid ORDER BY iddescription) ROW
FROM #D_DESCRIPTION d
)
UPDATE #D_DESCRIPTION
SET
requestid=c.requestid,
formid=c.formid,
term=
CASE d1.row
WHEN 1 THEN 'Term' + CAST(c.requestid AS CHAR(1))+' (category)'
ELSE 'Term' + CAST(c.requestid AS CHAR(1))+ CAST(c.requestid AS CHAR(1)) END
FROM cte d1
INNER JOIN #D_DESCRIPTION d2
ON d1.IdDescription=d2.IdDescription
INNER JOIN #CONCEPTS c
ON d1.conceptid=c.conceptid
WHERE d2.formid=0
August 10, 2010 at 4:18 pm
(
SELECT
d.*,
ROW_NUMBER() OVER(PARTITION BY d.conceptid ORDER BY iddescription) ROW
FROM #D_DESCRIPTION d
)
Thanks for your reply. As mentioned, I'm using SQL Server 2000. I believe the function Row_number was introduced with subsequent SQL Server versions only. That's actually my major challenge here.
Even though I have no idea what value you want to store in the DescriptionID column nor do I have a clue as how you'd calculate the number to be used in the Term column nor were your data ready to use...
DescriptionID is already populated.
The term column in the descriptions table would be as follows:
- first row: the term from the concepts table
- second row: the term from concepts table concatinated with the last letter. For example: Term5 would be Term55 (but this is easy)
Z
August 10, 2010 at 4:18 pm
Since you are using SQL 2000 you will probably find this easier.
UPDATE d_description SET RequestId = con.RequestId, FormId = Con.FormId,
DescriptionId = CASE WHEN IdDescription =
(SELECT MIN(IdDescription) FROM d_description d
WHERE d.ConceptId = con.ConceptId) THEN
MetaTerm
WHEN IdDescription =
(SELECT MAX(IdDescription) FROM d_description d
WHERE d.ConceptId = con.ConceptId) THEN
MetaTerm + RIGHT(MetaTerm,1)
END
FROM d_description
INNER JOIN concepts con
ON d_description.conceptId = con.conceptid
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
August 10, 2010 at 5:34 pm
Not a big deal, but in the future you're probably more likely to get relevant responses in the T-SQL forum for SQL Server 7 and 2000.
August 10, 2010 at 8:29 pm
Very smart. Thanks a brunch!
August 11, 2010 at 9:52 am
UPDATE d_description SET RequestId = con.RequestId, FormId = Con.FormId,
DescriptionId = CASE WHEN IdDescription =
(SELECT MIN(IdDescription) FROM d_description d
WHERE d.ConceptId = con.ConceptId) THEN
MetaTerm
WHEN IdDescription =
(SELECT MAX(IdDescription) FROM d_description d
WHERE d.ConceptId = con.ConceptId) THEN
MetaTerm + RIGHT(MetaTerm,1)
END
FROM d_description
INNER JOIN concepts con
ON d_description.conceptId = con.conceptid
Just a follow up question. What if the ConceptID column is empty in some rows in the descriptions table, how to do the join? I'm realizing that a long list of ConceptID is missing from the description table. Thanks!
August 12, 2010 at 10:28 am
ziedkallel (8/11/2010)
Just a follow up question. What if the ConceptID column is empty in some rows in the descriptions table, how to do the join? I'm realizing that a long list of ConceptID is missing from the description table. Thanks!
If you don't have a ConceptID how are you going to join the 2 tables? My understanding was that was the link between them. If it isn't then just use whatever column is the key column. If ConceptID is the key column and is missing in some rows then you have no link back to the Concepts table anyway and have no way to know what Metqterm to use right?
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
August 12, 2010 at 3:42 pm
Kenneth Fisher-475792 (8/12/2010)
ziedkallel (8/11/2010)
Just a follow up question. What if the ConceptID column is empty in some rows in the descriptions table, how to do the join? I'm realizing that a long list of ConceptID is missing from the description table. Thanks!If you don't have a ConceptID how are you going to join the 2 tables? My understanding was that was the link between them. If it isn't then just use whatever column is the key column. If ConceptID is the key column and is missing in some rows then you have no link back to the Concepts table anyway and have no way to know what Metqterm to use right?
Kenneth
Actually the descriptions table is created from scratch based on some (simple) business rules. I'm hoping I can rely on those business rules to do the joint. Here is what those business rules say:
For each row in table A (concepts) there are 2 rows in table B (descriptions). Following is a description of how we get the 2 rows in the descriptions table:
- The first row would contain ~ exactly the same information as in the row in the table concepts (lets call it rowCon1).
- The second row would contain the same information as in rowCon1 except that one column will perform some transformation to the expression located in the column metaterm (but this particular detail is under controle).
I spent a few hours thinking about a script and I came up with something that produces good result except that for some reason the first row is skipped and doesn't get inserted in the description table. The other issue I noticed was that there was 1 duplicate. Here is the code
GO
DECLARE @RequesTID int, @FormID varchar(50), @CONCEPTID varchar(18),
@METATERM varchar(255)
DECLARE CustList cursor
FOR
SELECT RequestID, FormID, CONCEPTID, METATERM
FROM con
ORDER BY RequestID
OPEN CustList
FETCH NEXT FROM CustList
While (@@FETCH_STATUS = 0)
Begin
FETCH NEXT FROM CustList
INTO @RequestID, @FormID, @CONCEPTID, @METATERM
INSERT INTO D_DESCRIPTION (RequestID, FormID, CONCEPTID, TERM, DESCRIPTIONTYPE, LANGUAGE) VALUES (@RequestID, @FormID,
@CONCEPTID, @METATERM, 'a', 'english')
INSERT INTO D_DESCRIPTION (RequestID, FormID, CONCEPTID, TERM, DESCRIPTIONTYPE, LANGUAGE) VALUES (@RequestID, @FormID,
@CONCEPTID, @METATERM, 'b', 'english')
End
Close CustList
Deallocate CustList
GO
UPDATE D_DESCRIPTION SET TERM=(select LEFT(TERM,(CHARINDEX('(', TERM))-1)) WHERE DESCRIPTIONTYPE=b
August 12, 2010 at 7:07 pm
ziedkallel (8/12/2010)
Actually the descriptions table is created from scratch based on some (simple) business rules. I'm hoping I can rely on those business rules to do the joint. Here is what those business rules say:
If your descriptions table really is wholly derived from the concepts table, you probably want to consider using a view instead.
CREATE VIEW Descriptions
AS
SELECT
RequestID
, FormID
, CONCEPTID
, METATERM AS TERM
, 'a' AS DescriptionType
, 'English' AS LANGUAGE
FROM Concepts
UNION
SELECT
RequestID
, FormID
, CONCEPTID
, LEFT(TERM,(CHARINDEX('(', TERM))-1))
, 'b'
, 'English'
FROM Concepts
With the view, your values are always up-to-date, so you don't need to worry about joining to update them.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 12, 2010 at 8:16 pm
Simple and neat! Thank you very much, Drew. It's much simpler than I thought it would be. Z
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply