By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

Update statement

P: n/a
Hi All,

I am not so proficient in SQL and seek your help.

I have a column by the name of Mask in a table, which has text eg.
(YYYYYYYNNNNYYYYYYYYYNNYYYY). I wanted to update one particular value
in that text. How would my update statement look like?

Below is my select statement.

select user, substring(mask, 50, 1) Authorisation from users where type
= 1 order by Authorisation desc

Below statement doesn't work.

update users set substring(mask, 50, 1) = 'Y' where user = 'me'

Regards,

Apr 28 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Munno (pa*********@gmail.com) writes:
I am not so proficient in SQL and seek your help.

I have a column by the name of Mask in a table, which has text eg.
(YYYYYYYNNNNYYYYYYYYYNNYYYY). I wanted to update one particular value
in that text. How would my update statement look like?

Below is my select statement.

select user, substring(mask, 50, 1) Authorisation from users where type
= 1 order by Authorisation desc

Below statement doesn't work.

update users set substring(mask, 50, 1) = 'Y' where user = 'me'


This is not Perl, so you can't do this.

Since you know substring, you might have figured this out on your own
already:

UPDATE users
SET mask = substring(mask, 1, @bitno -1) + @newval +
substring(mask, @bitno + 1, len(mask))
WHERE user = 'me'

I should add that this sort of mask is dubious from a design perspective.
It may be a lot better to make the various bits column in a table instead.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 28 '06 #2

P: n/a
On 28 Apr 2006 00:10:17 -0700, Munno wrote:
Hi All,

I am not so proficient in SQL and seek your help.

I have a column by the name of Mask in a table, which has text eg.
(YYYYYYYNNNNYYYYYYYYYNNYYYY). I wanted to update one particular value
in that text. How would my update statement look like?

Below is my select statement.

select user, substring(mask, 50, 1) Authorisation from users where type
= 1 order by Authorisation desc

Below statement doesn't work.

update users set substring(mask, 50, 1) = 'Y' where user = 'me'

Regards,


Hi Munno,

Recommendation: redesign the table. This design is a violation of first
normal form ("one value per column")

Quick kludge to get you running until yoou find the time to fix the
design: check out the STUFF function in Books Online.

UPDATE users
SET mask = STUFF(mask, 50, 1, 'Y')
WHERE user = 'me'

--
Hugo Kornelis, SQL Server MVP
Apr 28 '06 #3

P: n/a
Hugo,

I agree with you and Erland that the design is not the best one but I
can't fix it because that is how our vendor has done it. To change it
will be big change and that means money.

Thanks for your suggestions, it really helped me.

Munno

Apr 30 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.