SQL

samprg
samprg used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
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

Ephraim WangoyaSoftware Engineer

Commented:
UPDATE TABLE1
SET COLUMN = COLUMN + 1
WHERE COLUMN = 1

Author

Commented:
I need to increase the value I mean

Column
1 Be  2
1  Be 3
1  Be 4
1        5
1         6.......
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Commented:
If the table has a primary key:


UPDATE TABLE1 T
SET COLUMN =
(select count(*) from TABLE1 C where C.PK <= T.PK and C.COLUMN = 1)
WHERE COLUMN = 1

If no primary key, then you need to use TSQL and a cursor to loop thru the rows

Author

Commented:
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'.
SharathData Engineer

Commented:
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

Author

Commented:
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
Data Engineer
Commented:
Do you want to update your table first and then load the result into a new table? If yes, 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
select * into new_table from your_table
select * from new_table

Open in new window

Author

Commented:
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
SharathData Engineer

Commented:
>> 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?

Author

Commented:
I got it
Thank you soooooooooooooo much

Author

Commented:
Thanks Experts

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial