Click here to Skip to main content
15,867,937 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi..

How to increment two columns in a table at a time?


one column is id of the table which can be incremented using primary key.
how to increment second column ,the column name is 'count'. Both are of int data type.

Thanks..
Posted
Comments
Rockstar_ 29-May-13 1:00am    
Do you want to update the columns withe new values or you want to insert new row based on the previous row data?

you have not mention about how increament should be max+1 or simple...
you can set Identity Specification for 'Count' column also

Happy Coding!
:)
 
Share this answer
 
Comments
Bhargav.456 29-May-13 1:13am    
I want to increment '+1'.
Aarti Meswania 29-May-13 1:19am    
okCase1
e.g
1
2
3
4
now in above if i delete 4 and then add new recrd it will take entry 5
1
2
3
5
you want this?

or ...
Case2
1
2
3
if remove 2 then it reset series like 3 should be 2
1
2 <- 3
which case you want?
Bhargav.456 29-May-13 1:24am    
Thanks for the reply,
i need case2 pattern.
1 more query i need to ask.. Can I assign primary key for two columns?
Thanks
Aarti Meswania 29-May-13 1:33am    
it is called Composite key when two or more columns are combined and used as primary
visit this fr more info
http://stackoverflow.com/questions/1545571/how-do-i-make-a-composite-key-with-sql-server-management-studio

now, coming to the main point...
you have to go with trigger for case2

I will suggest you it will increase load and affect speed
so, if count column is used as serial no. then eliminate that column from table
and when write select query simply add row_number function based on primary key column or simply order by primary key column
Bhargav.456 29-May-13 1:42am    
Thanks.. Actually I have struct with this..

I have a column named as 'state' (contains different states e.g Assam, AP, Bangalore etc) based on the state name the column 'countadvertizment' should be incremented by '1'

E.g : If I have an ad under 'Assam', countadvertizment column should be '1'.
At further if I again enter an ad under Assam, it should be '2' (Incremented by 1)
Now, If I have an ad under 'AP', countadvertizment column should be '1'. Again if it is under 'Assam', countadvertizment column must be '3', as I have entered 2 ads under ASSAM.

Hope you got my question. Help me.

Thanks
I guess it really depends what you are doing, and what you are hoping to achieve. One option is to set the Count column to simply equal the primary key, but this seems pointless... Another would be to query the Max value of the primary key (or the sum, count of records etc) and add one.

What are you trying to accomplish?

In relation to your further information, you could use a query like this:

SQL
select count(*) from TABLENAME where State=@P_STATENAME


This will give you the number that already exist. Save this in a variable and add one. ;-)
 
Share this answer
 
v2
Comments
Bhargav.456 29-May-13 1:44am    
Thanks..

Actually,I have a column named as 'state' (contains different states e.g Assam, AP, Bangalore etc) based on the state name the column 'countadvertizment' should be incremented by '1'

E.g : If I have an ad under 'Assam', countadvertizment column should be '1'.
At further if I again enter an ad under Assam, it should be '2' (Incremented by 1)
Now, If I have an ad under 'AP', countadvertizment column should be '1'. Again if it is under 'Assam', countadvertizment column must be '3', as I have entered 2 ads under ASSAM.

Hope you got my question. Help me.

Thanks
_Damian S_ 29-May-13 2:12am    
Have updated solution based on your further information.
Bhargav.456 29-May-13 4:46am    
Thank you
Hello Bhargav,

For the First column you need to set it as a Primary key along with the Identity Specification.

For the second column you can use a trigger as :

SQL
CREATE TRIGGER [dbo].[Test_Trigger]
   ON  [dbo].[YourTableName]
   AFTER INSERT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    UPDATE a SET a.SecondColumn= max(a.SecondColumn)+1
    FROM YourTableName a JOIN inserted i ON a.FirstColumn= i.FirstColumn

END


Let me know if this helps.

Regards
Himanshu
 
Share this answer
 
v2
Comments
Bhargav.456 29-May-13 2:03am    
Thanks..

E.g : If I have an ad under 'Assam', countadvertizment column should be '1'.
At further if I again enter an ad under Assam, it should be '2' (Incremented by 1)
Now, If I have an ad under 'AP', countadvertizment column should be '1'. Again if it is under 'Assam', countadvertizment column must be '3', as I have entered 2 ads under ASSAM.

I want to implement with triggers.Does the above query works??
Himanshu__Bisht 29-May-13 2:23am    
Do you want you table as below :

First Ad CountAdvartizment
1 Assam 1
2 Assam 2
3 AP 1
4 Assam 3
5 AP 2
6 AP 3
7 Assam 4
Bhargav.456 29-May-13 3:55am    
Yes.
Bhargav.456 29-May-13 4:40am    
Thanks..
Can we use trigger of implement table data..??
You can try with the computed column
1.Create a function with Increment functionality.
2.Call the function in the computed column.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900