UPDATE column set to true.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

UPDATE column set to true....

Author

Topic

dst101
Starting Member

14 Posts

Posted - 2009-10-29 : 12:15:58

I want to set my chosen entry's column (GoLive) value to True whilst setting all the other entries of column (GoLive) to false.

How would I do this?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-10-29 : 13:14:07

perhaps
[code]
create table foo (idd int identity(1,1), golive bit)

insert into foo (golive) select 1 union all select 0 union all select 1
union all select 0 union all select 1
union all select 0 union all select 1
union all select 0 union all select 1
union all select 0 union all select 1

select * from foo

update foo set GoLive = case idd when 2 then 1 else 0 end
-- in the case statement, when YourIDvalueHERE, 2 is just a sample.
select * from foo

drop table foo

[code]

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dst101
Starting Member

14 Posts

Posted - 2009-10-30 : 11:01:22

Thanks DonAtWork!

So if I had columns:
id (int),
TheTitle (Text),
TheBody (Text),
GoLive(VarChar(5))

Would I write the UDPATE statement like so:

UPDATE foo
SET TheTitle = @TheTitle, TheBody=@TheBody
UNION
SET GoLive = CASE id WHEN 'True' THEN 'False' ELSE 0 END

Would this work?

Sorry if this is all beneath you guys!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-30 : 11:37:51

In your first post you have not given any information about your table and data in it.
So DonAtWork has build its own example table and data but you are not able to understand and convert it to what you need.

So the solution is:
give us table structure (like you did in your second post)
give us some sample data
give us the data / wanted result after that update

I think that isn't too heavy.

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-10-30 : 11:41:20

more like this:


UPDATE	foo
SET	TheTitle = Case WHEN id = @id THEN @TheTitle ELSE TheTitle End,
	TheBody = Case WHEN id = @id THEN @TheBody ELSE TheBody End,
	GoLive = Case WHEN id = @id THEN 'True' ELSE 'False' END

where @id is the id of the record you want to set to 'True'

Now, why is true/false set to a varchar instead of a bit field?

Also, what are you storing in those text columns? I'm betting they can be varchar. Especially title.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-30 : 11:41:33

That won't work for several reasons. id is an int, it isn't true or false. What exactly is the criteria you want to update GoLive by?

Jim

Everyday I learn something that somebody else already knew

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-10-30 : 11:43:47

since everything except for one record needs to be false, is better to just set that one to false then perform on update for the True record


UPDATE	foo
SET	TheTitle = @theTitle,
	theBody = @theBody,
	GoLive = 'True'
WHERE	id = @id

dst101
Starting Member

14 Posts

Posted - 2009-10-30 : 12:16:38

Okay, I appologise for not doing the neccessary, here is my table [tbl_messages]:

id (int) - primary key
TheTitle (Text),
TheBody (Text),
GoLive (varchar(5))

I know I should change this to:

id (int) - primary key
TheTitle (VarChar(250)),
TheBody (Text),
GoLive (bit)

Scenario:
User selects specific entry to be a value of True, then setting the rest of the entries to False.

My SQL:
UPDATE tbl_messages
SET TheTitle = @TheTitle, TheBody=@TheBody WHERE id =@id
UNION
SET GoLive = CASE id WHEN '1' THEN '0' ELSE 0 END

Would that work?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-30 : 12:30:18

I don't see where the other columns come in to play

UPDATE tbl_messages
SET GoLive = CASE WHEN id = @id THEN 1 ELSE 0 END

Jim

Everyday I learn something that somebody else already knew

dst101
Starting Member

14 Posts

Posted - 2009-10-30 : 12:39:17

Okay, but I need to update the current row as well as updating the rest of the rows.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-30 : 12:45:03

Dude!
See russells post [Posted - 10/30/2009 : 11:41:20 ]
and replace true and false by 1 and 0

No, you're never too old to Yak'n'Roll if you're too young to die.

dst101
Starting Member

14 Posts

Posted - 2009-11-02 : 06:32:21

Tried what webfred had suggested; looking at what russell had posted but this pulls errors:
The CLR type does not exist or you do not have permission to access it..

I thought this:

UPDATE foo SET
TheTitle = @TheTitle,
TheBody = @TheBody,
GoLive = CASE id WHEN 1 THEN 0 ELSE 0 END WHERE (id = @id)

dst101
Starting Member

14 Posts

Posted - 2009-11-02 : 06:48:17

quote:

I don't see where the other columns come in to play

UPDATE tbl_messages
SET GoLive = CASE WHEN id = @id THEN 1 ELSE 0 END

The user should be able to alter the fields: TheTitle, TheBody and GoLive in one motion.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-02 : 11:11:43

that's what i posted. show us your code and the table definition.

dst101
Starting Member

14 Posts

Posted - 2009-11-02 : 11:43:43

okay decided to just simplify the whole thing, I now just have to update the GoLive relevant row to true and set the rest to false.
I tried this:

UPDATE tbl_messages
SET GoLive = CASE WHEN id = @id THEN 1 ELSE 0 END

I get this error when trying it in SQL:
The CLR type does not exist or you do not have permission to access it

My tbl_messages is set up like so:

id (int) primary key
TheTitle (varChar(250))
TheBody (text)
GoLive (bit) default (0)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-02 : 12:48:51

I have tried the following with NO errors:
create table #tttt(
id int,
TheTitle varChar(250),
TheBody text,
GoLive bit default (0)
)

select * from #tttt

declare @id int
set @id=0
UPDATE #tttt
SET GoLive = CASE WHEN id = @id THEN 1 ELSE 0 END

So I believe that you did not post the original statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

dst101
Starting Member

14 Posts

Posted - 2009-11-03 : 02:36:31

webfred, I declared the id at the start and it works.
I'm using Visual web developer 2008 express edition, would that account for the errors that i was getting?

quote:

So I believe that you did not post the original statement.

What do you mean by that?

In any case thank you all for your patience, I'm just glad it seems to work now.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-11-03 : 09:14:43

just to repost the important part of my first post...

update foo set GoLive = case idd when 2 then 1 else 0 end

except for the column names, it gives the same results as

SET GoLive = CASE WHEN id = @id THEN 1 ELSE 0 END

from Fred.

Have a weissen on me, Fred.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-03 : 09:39:16

I will have a Jever on you!
I'm not a Bavarian!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources