July 22, 2008 at 10:56 am
I have a table with 106 columns, and I would like to change the predefined value 'NULL' to other value, that can be the same for all columns with this value. There is another way instead of typing all the columns name by hand, like: UPDATE [table_name] SET [column_name1,...,column_name106] where [condition]
July 22, 2008 at 5:13 pm
Dynamic sql is an option (one timer).
create a cursor to loop through your columns (in sql2005 the cursor would reference information_schema.columns)
@aSql = 'update mytable set ' + @aColumn + ' = YOURVALUEHERE WHERE ' + @aColumn + ' = ''null'' '
EXEC SP_EXECUTESQL @aSql
Note: this only allows one value.
It would also be possible to run the script above and instead of executing it. Display the output and then manually input the defaults desired.
Note: this is assuming your database has the value = 'Null' in the column. If instead the column is null then @aColumn is null.
July 23, 2008 at 10:33 am
Hi there!Thanks for your reply
Yes,I'm working with sql 2005. So an example of code to solve my problem could be like this:
declare @aux as nvarchar(100)
declare client_cur cursor FOR
select * from Clients where client='123'
for update
open client_cur
fetch next from client_cur into @aux
WHILE @@FETCH_STATUS = 0
begin
update Clients set @aux = 'testing' WHERE @aux = 'null'
end
close client_cur
DEALLOCATE client_cur
I've tried the above code but it occurs an error, because it needs more than one variable to store each column value.
"Cursorfetch: The number of variables declared in the INTO list must match that of selected columns."
How I can solve this problem dynamically!? 😉
Thanks
July 23, 2008 at 3:24 pm
michael_pereira (7/23/2008)
declare client_cur cursor FORselect * from Clients where client='123'
for update
Change above as:
declare client_cur cursor for
select column_name
from information_schema.columns
where Table_Name = 'clients'
July 24, 2008 at 8:57 pm
Hi there! Thanks for your reply 😉
And I'm in the right way to solve my problem, that its little! I can access to the columns, but I can't change its value!
Can you take a look to the code, in way to determine the problem 😀
declare @column_name as nvarchar(100)
declare client_cur cursor FORWARD_ONLY for
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='clients'
open client_cur
fetch next from client_cur into @column_name
WHILE @@FETCH_STATUS = 0
begin
Print 'Processing the column: '+@column_name
UPDATE Clients SET @column_name = 'new value'
where @column_name is null
fetch next from client_cur into @column_name
end
close client_cur
DEALLOCATE client_cur
Once again, thanks 🙂
July 24, 2008 at 10:25 pm
You need a variable to do this.
select @cmd = 'update tableA set '
-- cursor start here
select @cmd = @cmd + @column_name + '= ''value'' where ' + @column_name + ' is null'
-- close cursor
exec( @cmd)
July 25, 2008 at 12:52 pm
Steve Jones - Editor (7/24/2008)
You need a variable to do this.select @cmd = 'update tableA set '
-- cursor start here
select @cmd = @cmd + @column_name + '= ''value'' where ' + @column_name + ' is null'
-- close cursor
exec( @cmd)
One typo: 😉 change the code order as:
...
...
exec (@cmd)
-- close cursor
July 27, 2008 at 2:12 am
I think the below code will solve your problem,
create table #a (nam varchar(10))
insert into #a values('aa')
insert into #a values('ba')
insert into #a values('ca')
with cte as
(
select 1 as id
)
update #a set nam = (select * from cte)
select * from #a
Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:
http://venkattechnicalblog.blogspot.com/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply