August 29, 2011 at 11:41 am
Hi Everyone,
I come across a problem and don't know what to do?
I have 2 tables. TableA and TableB.
TableA (All INT data type)
ID Percent Importance
And TableB had ID column and some other columns.
Now I want to update TableB ID column with TableA Id column and number of rows I want to update in TableB is the value of Percent column in TableA.
suppose in TableA , I have Id =2, Percent=30 and Importance=100 then I want 30% rows from TableB need to be update with Id=2.
Please help me what to do??
August 29, 2011 at 12:03 pm
krishusavalia (8/29/2011)
Hi Everyone,I come across a problem and don't know what to do?
I have 2 tables. TableA and TableB.
TableA (All INT data type)
ID Percent Importance
And TableB had ID column and some other columns.
Now I want to update TableB ID column with TableA Id column and number of rows I want to update in TableB is the value of Percent column in TableA.
suppose in TableA , I have Id =2, Percent=30 and Importance=100 then I want 30% rows from TableB need to be update with Id=2.
Please help me what to do??
A good place to start would be reading the article linked in my signature.
I can't begin to understand your business rules here. Because there are 30% of the rows in TableA with an ID of 2 you want to update 30% of TableB? Which 30%? A Random 30%? I am not sure why you say you need help with a cursor. Help us help you by making it clear what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2011 at 12:43 pm
There are some questions here that need to be answered before much can be done with this.
First, what is the "Importance" column in TableA? Can you have repeating values in this column?
Second, is there a sequence to the rows in TableB that you want to update in order? E.g.: If you want to update the first 30 rows with ID 2, and the next 20 rows with ID 3, how are you sorting these? Or are you?
The easiest way I can think of to do this is generate a running total for TableA, ordered by Priority (assuming that's what that column is for), and then use that in a Cross Apply for TableB, to select the top X rows, where X = the running total, then invert the sort order and select top Y, where Y = the number of rows needed based on the percentage figure, and use the result of that as the Source for a Merge statement. But there are a lot of assumptions in that plan.
Third, what do you want to do if there are rows left over, either because the Percent column in TableA doesn't add up to exactly 100, or because of rounding errors if the rows in TableB don't divide evenly into the distributions of percentages in TableA? (Create a TableB with 101 rows to see how that works out.)
Fourth, my solution summary above assumes you're using SQL 2008. Is that correct?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 29, 2011 at 1:04 pm
Sorry for not giving the enough detail.
1)Importance in TableA is not repeating.
2) I am sorting on Importance column.
3) percent column total is exact 100.
4) I am using SQl servere R2
August 29, 2011 at 1:04 pm
Also Sorting TableB on ID column for update.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply