January 6, 2009 at 9:54 am
Hi,
I'm looking for some guidance on an update query which is proving a little trickier than I first imagined. It's quite difficult to explain so please bear with me! I have a table which looks like the one below.
tocid prop_id pos str_val
221300NULL
221310ABC Limited
221320NULL
221330NULL
221350NULL
221360NULL
221370NULL
221380NULL
221390NULL
231300NULL
231310XYZ Limited
231320NULL
231330NULL
231350NULL
231360NULL
231370NULL
231380NULL
231390NULL
What I would like to do is update the str_val field where prop_id = 130 using a value derived from str_val where prop_id = 131 (using a join for example).
In real world terms, the value of str_val where prop_id = 131 indicates a company name. Another table holds this value along with a shortened company code (e.g. "ABCLIM"). I want my query to update the company code field using the company name as a lookup.
Can anyone provide any assistance on this - do I need to look at a different strategy?
Thanks,
Nigel.
January 6, 2009 at 10:30 am
January 6, 2009 at 11:20 am
Based on what I heard, this?
UPDATE TABLE
SET str_val = x.str_val
FROM TABLE x
WHERE x.prop_id = 131
and TABLE.prop_id = 130
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2009 at 12:51 pm
I think that Grant forgot one link
UPDATE TABLE
SET str_val = x.str_val
FROM TABLE x
WHERE x.prop_id = 131
and TABLE.prop_id = 130
and TABLE.tocid = x.tocid
* Noel
January 6, 2009 at 1:08 pm
Oopsie. Thanks Noel!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2009 at 1:34 pm
Review the article I link to in my signature on how to post a question to get a better/faster answer. Providing the create statements, insert statements for sample data and expected output will help us help you a lot better.
Now - how do you determine that prop_id 130 should get it's str_value from prop_id 131? Not real clear on that logic.
Here is my guess at what you are looking for:
;WITH nonNulls (prop_id, str_value)
AS (SELECT prop_id
,str_value
FROM yourtable
WHERE str_value IS NOT NULL)
SELECT ...
,(SELECT MIN(prop_id)
FROM nonNulls
WHERE prop_id > t.prop_id) AS new_str_value
FROM yourtable t
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 6, 2009 at 2:09 pm
Grant Fritchey (1/6/2009)
Oopsie. Thanks Noel!
I have witnessed the quality of your answers. I know it was just a glitch 😀
* Noel
January 7, 2009 at 11:15 am
Thanks for your input on this guys, I've not explained what I want very well though so here goes a 'proper' post!
I'm concerned with two tables created as follows:
IF OBJECT_ID('TempDB..#propval','U') IS NOT NULL DROP TABLE #propval
CREATE TABLE #propval (TocID INT, prop_id INT, pos smallint, str_val nvarchar(4000))
INSERT INTO #propval (TocID, prop_id, pos, str_val)
SELECT '22','130','0', NULL UNION ALL
SELECT '22','131','0','ABC LIMITED' UNION ALL
SELECT '22','132','0', NULL UNION ALL
SELECT '22','133','0', NULL UNION ALL
SELECT '22','134','0', NULL UNION ALL
SELECT '22','135','0', NULL UNION ALL
SELECT '22','136','0', NULL UNION ALL
SELECT '22','137','0', NULL UNION ALL
SELECT '22','138','0', NULL UNION ALL
SELECT '23','130','0', NULL UNION ALL
SELECT '23','131','0','XYZ LIMITED' UNION ALL
SELECT '23','132','0', NULL UNION ALL
SELECT '23','133','0', NULL UNION ALL
SELECT '23','134','0', NULL UNION ALL
SELECT '23','135','0', NULL UNION ALL
SELECT '23','136','0', NULL UNION ALL
SELECT '23','137','0', NULL UNION ALL
SELECT '23','138','0', NULL
IF OBJECT_ID('TempDB..#Customers','U') IS NOT NULL DROP TABLE #Customers
CREATE TABLE #Customers (CustomerCode nvarchar(10), CustomerName nvarchar(250))
INSERT INTO #Customers (CustomerCode, CustomerName)
SELECT 'ABCLIM', 'ABC LIMITED' UNION ALL
SELECT 'XYZLIM', 'XYZ LIMITED'
The prop_val table contains the metadata/index data of two documents in a document management system (there are thousands but I want to keep things simple) - document 1 has a tocid value of 22, document 2 has a tocid of 23. Each record represents an index field for these documents. I want to use the value of the company name field to lookup against the customer table and return the customer code (ABCLIM or XYZLIM for example) and populate the previous row (where prop_id = 130).
The end result should end up looking like this:
tocid prop_id pos str_val
22 130 0 ABCLIM
22 131 0 ABC LIMITED
22 132 0 NULL
22 133 0 NULL
22 134 0 NULL
22 135 0 NULL
22 136 0 NULL
22 138 0 NULL
22 139 0 NULL
23 130 0 XYZLIM
23 131 0 XYZ LIMITED
23 132 0 NULL
23 133 0 NULL
23 134 0 NULL
23 135 0 NULL
23 136 0 NULL
23 138 0 NULL
23 139 0 NULL
I hope this makes some more sense, if not I give up!
Nigel.
January 7, 2009 at 11:43 am
Based upon your table definitions and sample data (thanks) this will work.
Update #propval
Set str_val = (Select CustomerCode
From #Customers c
Join #propval p On p.str_val = c.CustomerName
Where p.TocID = #propval.TocID
And p.prop_id = '131')
Where prop_id = 130;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 8, 2009 at 2:09 am
Nice one Jeffrey - that's spot on.
Many thanks for your help on this one.
Nigel.
January 8, 2009 at 9:12 am
Nigel - that is just one of the reasons we ask for the questions to be posted the way we do. This way we can test a solution that meets your expectations a lot easier.
Glad I could help.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply