Link to home
Start Free TrialLog in
Avatar of samprg
samprg

asked on

SQL

Hello,

I need to change value in column like the folowing:

Whil
If Table.Column=1
update Table set Table.Column +1

But I can not use "IF" with Table.column  
Thank you

Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

If you mean you want to increment all values:

UPDATE [Table]
SET [Column] = [Column] + 1

Open in new window


To do that only where the value is already 1:

UPDATE [Table]
SET [Column] = [Column] + 1
WHERE [Column] = 1

Open in new window

UPDATE TABLE1
SET COLUMN = COLUMN + 1
WHERE COLUMN = 1
Avatar of samprg
samprg

ASKER

I need to increase the value I mean

Column
1 Be  2
1  Be 3
1  Be 4
1        5
1         6.......
SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of samprg

ASKER

gives me errore

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'COLUMN'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'COLUMN'.
try this.
;with cte as (select col1,ROW_NUMBER() over (partition by col1 order by col1)+col1 rn from your_table)
update cte set col1 = rn where col1 = 1

Open in new window

here is an example.
declare @table table (col1 int)
insert @table values (1),(1),(1),(1),(1)
;with cte as (select col1,ROW_NUMBER() over (partition by col1 order by col1)+col1 rn from @table)
update cte set col1 = rn where col1 = 1
select * from @table
/*
col1
2
3
4
5
6
*/

Open in new window

Avatar of samprg

ASKER

Thank Sharath_123

I need put the resul into new table

;with cte as (select col1,ROW_NUMBER() over (partition by col1 order by col1)+col1 rn from your_table)
update cte set col1 = rn where col1 = 1
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of samprg

ASKER

I am sorry, the plan changed.

I need to >>
I have table T1 incloude 30 rows,I need to make doublicate 50 times for each Row

the table it shoul be 1500

I need to use this code
;with cte as (select col1,ROW_NUMBER() over (partition by col1 order by col1)+col1 rn from your_table)
update cte set col1 = rn where col1 = 1

Thank you a lot
>> I have table T1 incloude 30 rows,I need to make doublicate 50 times for each Row
Do you have 30 rows with different number for  1 to 30 for ID column? If you want 50 duplicate records for each ID, how do you want to update the ID. Can you post your expected result?
Avatar of samprg

ASKER

I got it
Thank you soooooooooooooo much
Avatar of samprg

ASKER

Thanks Experts