Update All Null of Field (data Tape "Bit") To False

Question

Update All Null of Field (data Tape "Bit") To False

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

babak3334000 SSChasing Mays Points: 637 More actions · Answer 1

My code is correct

I Tested

USE TestDb;
DECLARE @Table NVARCHAR(MAX),
@Col NVARCHAR(MAX)
DECLARE Table_Cursor CURSOR
FOR
SELECT a.name,b.name
FROM sysobjects a,syscolumns b
WHERE a.id = b.id
AND a.xtype = 'u'
AND (
b.xtype = 104
)
OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @Table,@Col
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC
(
'update [' + @Table + '] set [' + @Col + ']=0
where ([' + @Col + '] is Null)'
)
FETCH NEXT FROM Table_Cursor INTO @Table,@Col
END CLOSE Table_Cursor DEALLOCATE Table_Cursor

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

babak3334000 (1/17/2012)
mhike2hale (1/17/2012)
My script works.
I think you did not execute this line:
exec(@queries)--execute your query
This will execute the generated scripts and make changes to your database.
Ok very Good
I quickly did I forget it

You don't seem to understand the code you are using

Don't execute the code in Production unless you understand it thoroughly

You can't blame anybody on this site if the code produces unexpected results

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

SQL Kiwi (1/16/2012)
SQLRNNR (1/16/2012)
babak3334000 (1/16/2012)
For every Column Data Type Is "Bit"
Example :
select * from syscolumns where xtype = 104 --: bit
...I would seriously consider revising your database design if every column in the database is a bit field.
He means 'for every column that has the bit data type', not 'every column has the bit data type' 😉

OIC 😀

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mateus1 Newbie Points: 3 More actions · Answer 4

DECLARE

@Table NVARCHAR(MAX),

@Col NVARCHAR(MAX)

DECLARE Table_Cursor CURSOR

FOR

SELECT a.name,b.name

FROM sysobjects a,syscolumns b

WHERE a.id = b.id

AND a.xtype = 'u'

AND (

b.xtype = 104

)

OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @Table,@Col

WHILE (@@FETCH_STATUS = 0)

BEGIN

EXEC

(

'update [' + @Table + '] set [' + @Col + ']=0

where ([' + @Col + '] is Null)'

)

FETCH NEXT FROM Table_Cursor INTO @Table,@Col

END CLOSE Table_Cursor DEALLOCATE Table_Cursor

hello, I used this query and it worked fine, however I need for a table only, can someone help me?

djj SSCoach Points: 18831 More actions · Answer 5

Sorry jumpped in without reading all posts. Missed the multiple pages