August 4, 2010 at 12:06 pm
Here is a sample table:
CREATE TABLE [dbo].[tbl_My_Test](
[intMain_ID] [int] NOT NULL,
[strJob_ID] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[strClass_ID] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[strFunction_ID] [nvarchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fPri_Value] [real] NOT NULL,
CONSTRAINT [PK_tbl_My_Test] PRIMARY KEY CLUSTERED
(
[intMain_ID] ASC,
[strJob_ID] ASC,
[strClass_ID] ASC,
[strFunction_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
First I agree, doing a bulk update is faster, easier, cleaner, etc... However at this point I don't think it's do-able (another way of saying I don't know how).
Could someone help me with this? I have the above table with several (thousand) records. Based on the Job, Class and Function I need to preform a calculation (data from several tables, mins, max, etc...) and update the Value of that specific record or records.
i.e.
A) I'm going to use a 'Select Distinct' to insert data to a seperate table for Job, Class and Function only. Yep, you're right I should do this with a Temp Table but I'm just now learning how to open a record set and I don't want to ask too much or extend myself into a hole if it is really difficult.
B) I need to step though the these records looking at each unique Job, Class and Function.
C) Based on this I will update the original table 'Value' field using a where matching Job, Class and Function in Step B.
In an earlier post someone very helpful gave me some code for loops. But now, using the Loop I now need to open the record set, set to the first record, read the data in the necessary fields, move the next record and close the record set when I reach the EOF.
I've done this in VBA and it seems fairly straight forward but I've never done this in MSSQL and could really use a little direction.
I appreciate any help I could get.
Thank you,
August 4, 2010 at 12:19 pm
I'm not sure exactly what you're trying to do, but it sounds like you are trying to do an UPDATE ... FROM, which allows you to update a table based on the results of a query:
UPDATE [Table]
SET [Field] = [OtherTable].[Field]
FROM [OtherTable]
WHERE [Field2] = [OtherTable].[Field2]
You can get pretty complicated with it, including JOINs and other constructs. You can also reference fields on the table you are updating in conjunction with fields from tables in the FROM clause:
UPDATE [Table]
SET [Field] = [Field2] * [OtherTable].[Field] + [AnotherTable].[Field]
FROM [OtherTable]
LEFT JOIN [AnotherTable] ON [OtherTable].[AnontherTableID] = [AnotherTable].[ID]
WHERE [Field3] = [OtherTable].[Field3]
August 4, 2010 at 12:28 pm
I use Update Where often. Sometimes with several Joins and Case (Select) as part of the Set. You're right these can get complicated and one I'm using right now is rather complex and long.
However in this case I don't believe I can do a "simple" Update Where" because the calculation is very complex, for me atleast. That is the reason I'm trying to move though the records one at a time. I know it's much slower but each unique record requires a completely different set of calculated values.
Believe me, if I can figure out how to do all the joins, lookups, with limits (high and low), exceptions etc in a single Set statement, I would use the Update Where in a heart beat. But for now, it doesn't appear so.
August 4, 2010 at 12:36 pm
Just to clarify: every UPDATE query should have a WHERE clause. I've often thought it should be a required clause, because something like "UPDATE [Table] SET [Field] = 1" updates every row in the table. UPDATE without WHERE is dangerous. Personally, I think you should have to explicitly state "WHERE 1=1" if that's what you mean to happen, but I digress...
Not every UPDATE query will have a FROM clause. It sounds like you're actually taking about UPDATE..FROM when you say UPDATE..WHERE, but I wanted to make sure.
Chances are you can still do what you're trying to do with an UPDATE..FROM. You mentioned having to do a SELECT DISTINCT. That could be done via a subquery in the FROM clause:
UPDATE [Table]
SET [Field] = [Subquery].[Field]
FROM
(SELECT DISTINCT [Field] FROM [OtherTable]) AS Subquery
WHERE ....
If you can SELECT it, you should be able to turn it into an UPDATE query. If you need more help, I'll need more information. If you already have a SELECT query that gets the data you want, that would help. Otherwise, I need to know what you're trying to do and I can help script it for you.
August 4, 2010 at 12:43 pm
I apoligize for not being clear, I'm still new at this and I do appreciate your help. However I actually have used Updates, Updates with Where, Updates with Where using Select From, etc. No problem.
In this case the Set value is far more complicated than usual so that approach is not working.
I'm trying to learn how to open a record set (the table above) and step though it. The ole' Row by agonizing row, method and could use any help with this.
Thank you again,
August 4, 2010 at 1:42 pm
How about this problem. I found this on MSDN and can easily adapt it to my table.
DECLARE Employee_Cursor CURSOR FOR
SELECT BusinessEntityID, JobTitle
FROM AdventureWorks2008R2.HumanResources.Employee;
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor;
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO
It opens the records set, and scrolls through the records one at a time.
After the Fetch Next statements I will place my calculations and Update statements.
BUT!!! Using this example how do I bring these fields (BusinessEntityID and JobTitle) into temp variables so that I may use them in my calculations?
I really could use some help,
Thanks,
August 4, 2010 at 2:01 pm
I apoligize everyone,
A little more research and I found it.
But if anyone needs it, here it is:
'Using FETCH to store values in variables'
http://msdn.microsoft.com/en-us/library/ms180152.aspx
USE AdventureWorks2008R2;
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @LastName varchar(50), @FirstName varchar(50);
DECLARE contact_cursor CURSOR FOR
SELECT LastName, FirstName FROM Person.Person
WHERE LastName LIKE 'B%'
ORDER BY LastName, FirstName;
OPEN contact_cursor;
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM contact_cursor
INTO @LastName, @FirstName;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
PRINT 'Contact Name: ' + @FirstName + ' ' + @LastName
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM contact_cursor
INTO @LastName, @FirstName;
END
CLOSE contact_cursor;
DEALLOCATE contact_cursor;
GO
But if anyone needs it, here it is.
Thank you,
August 4, 2010 at 3:05 pm
Ken,
You're not going to find too many people here very wild about your cursor solution. There are many of us that will be willing to help you get rid of it, and the performance difference will blow you away.
So, if you would like for us to help you, you will need to:
1. Read the first link in my signature, and post the table DDL. Sample data to work with. Expected output based on the sample data.
2. Your "complex" calculation (if you can do it in a cursor, you can do in in the update statement!)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 4, 2010 at 3:13 pm
I'm with Wayne. I simply can not bring myself to help someone devise a cursor-based solution when another solution is available. I tried. Even if your current solution is not time-critical, starting down the path of the cursor is a dangerously seductive road. They're so easy to conceptialize that they'll turn into your go-to guy whenever you can't wrap your mind around a set-based solution. Eventually, you'll have 50+ cursors running on your server before you truly start to notice how truly bad for performance they can be. Don't get me wrong - there are problems for which a cursor is an appropriate solution, this is definitely not one of them.
August 4, 2010 at 3:19 pm
I'm with Wayne as well, so that makes three of us.
August 5, 2010 at 9:22 am
Perhaps when I get the calcualtion finished I'll be back for your help.
Honestly it's a bit lengthy and hard to follow just giving you the list of tables.
This does not even consider the actual calculation.
However, how about this.
Table A as above with intMain_ID
Table B can be joined on intMain_ID BUT it has multiple rows and I don't know how many.
If I join A to B I end up with multip rows for each original record. Using a multiple Unions could work IF I knew how many rows was in Table B.
Any ideas?
Thanks,
August 5, 2010 at 10:07 am
Ken@Work (8/5/2010)
Perhaps when I get the calcualtion finished I'll be back for your help.Honestly it's a bit lengthy and hard to follow just giving you the list of tables.
This does not even consider the actual calculation.
However, how about this.
Table A as above with intMain_ID
Table B can be joined on intMain_ID BUT it has multiple rows and I don't know how many.
If I join A to B I end up with multip rows for each original record. Using a multiple Unions could work IF I knew how many rows was in Table B.
Any ideas?
Thanks,
Join on a subquery of Table B:
FROM
[TableA]
LEFT JOIN (
SELECT intMain_ID, SUM([NumericField]) AS NumericFieldSum
) B ON A.intMain_ID = B.intMainID
This is the simplest way. If you have non-summable fields, you'll need to somehow pick the most relevant field. I can't tell you how to do so without knowing what TableB looks like and knowing what would make it relevant.
August 5, 2010 at 10:46 am
My bad,
When I said multiple rows I didn't mean data that I could sum.
i.e.
Field_1, Field_2
Main_ID, Data_A
Main_ID, Data_B
Main_ID, Data_C
I would rather
Main_ID, Data_A, Data_B, Data_C.
I usually do this with a Join (or multiple joins). In this case I don't know how many joins to use because the number of rows may be different for each Main_ID.
Sorry about that.
August 5, 2010 at 11:32 am
Sounds like a dynamic cross-tab / pivot. See the two articles in my signature (Parts 1 and 2) - part 2 covers a dynamic cross-tab.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply