Update a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update a table

Author

Topic

Programmer666
Starting Member

4 Posts

Posted - 2009-10-09 : 06:46:45

Hello to everyone this is my first post.
I am developing a share maintenance software.
I am using SQL SERVER 2005
I have a table like this-

InvestorID nvarchar(10) NotNull,
InvestorName nvarchar(20) NotNull,
Shares Int NotNull,
ShareAllotmentNoFrom Int
ShareAllotmentNoTo Int

InvestorId is the primary key.

Now when the initial entry is made to the table, Only the InvestorID,
InvestorName and Shares are entered. The ShareAllotmentNoFrom field
and ShareAllotmentNoTo field are left null.Now after the details are checked i like to fill the ShareAllotmentNoFrom fields and
ShareAllotmentNoTo fields.Values will be inserted like this say the
1st Investor bought 3 share his ShareAllotmentNoFrom will be 1 and ShareAllotmentNoTo will be 3. Now the 2nd Investors ShareAllotmentNoFrom will begin from 4 and his ShareAllotmentNoTo will be 4 + no of shares. Now how can I update a whole table like this using a stored procedure. Thank you

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-09 : 07:13:00

Create a Table solely holding a 'counter', if you know what I mean? So you can keep adding to the value everytime stuff is added you use the counter as the number to add to and assign the number at the start before it is added to, and after to give your range thingy for each transaction. You know what I mean?

[ /fail at query]

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-09 : 07:18:06

(Updating your ShareAllotmentFrom before you add to the counter, and updating your ShareAllotmentTo after the counter has been added to). tell me if you don't get what I am trying to get at, and I will give you a more step by step explanation of what my solution would be to this problem.

[ /fail at query]

Programmer666
Starting Member

4 Posts

Posted - 2009-10-09 : 07:26:51

Thanks for replying Harry.I dont understand what you mean. I think i have to make it a little more clear. I want to the whole process to be carried through a single transaction.I think i can get the ShareAllotmentFrom by Max(ShareAllotmentTo)+1 . But i dont know how to do it for each row. I want to do the whole process by clicking a button or something like that. I hope I made myself a little more clear this time.

P.S: I am using VB 2005 as a front end.

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-09 : 08:19:12

Sorry, not sure how to go about doing that. The counter is probably the easiest solution I can offer you. But passed that I don't know. :( Madhivanan (Or his colleagues) can help, they are guru level.

[ /fail at query]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 12:02:49

I think this is what you want


CREATE TABLE #Counter
(ID int IDENTITY(1,1) NOT NULL,
InvestorID nvarchar(10) NotNull,
InvestorName nvarchar(20) NotNull,
Shares Int NotNull
)


INSERT INTO #Counter (InvestorID,
InvestorName,
Shares)
SELECT InvestorID,
InvestorName,
Shares
FROM Yourtable

Update t
SET t.ShareAllotmentNoFrom=COALESCE(c2.SharesBefore,0)+1
t.ShareAllotmentNoTo=COALESCE(c2.SharesBefore,0)+ 1 + t.Shares
FROM yourtable t
INNER JOIN #Counter c1
ON c1.InvestorID =t.InvestorID 
AND c1.InvestorName =t.InvestorName 
AND c1.Shares=t.Shares
OUTER APPLY (SELECT SUM(Shares) AS SharesBefore
             FROM #Counter
             WHERE ID <c1.ID)c2

Programmer666
Starting Member

4 Posts

Posted - 2009-10-09 : 14:24:51

Thanks Visakh. But i receive an error when I try to run it.

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 14:34:16

that means the collation of one of columns invloved in temporary table and yours is different. just add this to all colums in on condition


.....
Update t
SET t.ShareAllotmentNoFrom=COALESCE(c2.SharesBefore,0)+1
t.ShareAllotmentNoTo=COALESCE(c2.SharesBefore,0)+ 1 + t.Shares
FROM yourtable t
INNER JOIN #Counter c1
ON c1.InvestorID collate database_default=t.InvestorID 
AND c1.InvestorName collate database_default =t.InvestorName 
AND c1.Shares=t.Shares
OUTER APPLY (SELECT SUM(Shares) AS SharesBefore
             FROM #Counter
             WHERE ID <c1.ID)c2

Programmer666
Starting Member

4 Posts

Posted - 2009-10-09 : 15:20:51

Thanks visakh
Its working.But there is one little problem here.

InvestorId InvestorName Shares ShareNoFrom ShareAllotmentNoTo
1 SMITH 2 1 3
2 DAVID 50 3 53
3 BRYAN 10 53 63

The shareallotmentNofrom of David should begin from 4 (i.e. 3+1) and
shareallotmentNofrom of Bryan should begin from 54 (i.e. 53+1).

I hope you understand what I mean ?

And what is collation?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-11 : 14:36:51

[code]
.....
Update t
SET t.ShareAllotmentNoFrom=COALESCE(c2.SharesUpto,0)+ 1 - t.Shares
t.ShareAllotmentNoTo=COALESCE(c2.SharesUpto,0)+ 1
FROM yourtable t
INNER JOIN #Counter c1
ON c1.InvestorID collate database_default=t.InvestorID
AND c1.InvestorName collate database_default =t.InvestorName
AND c1.Shares=t.Shares
OUTER APPLY (SELECT SUM(Shares) AS SharesUpto
FROM #Counter
WHERE ID <=c1.ID)c2
[/code]

http://www.databasejournal.com/features/mssql/article.php/3302341/SQL-Server-and-Collation.htm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources