Update statement

[Munno]

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,

[Erland Sommarskog]

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

[Hugo Kornelis]

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

[Munno]

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