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.
(YYYYYYYNNNNYYY YYYYYYNNYYYY). 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]

Re: Update statement

Munno (patelroshan@gm ail.com) writes:[color=blue]
> 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.
> (YYYYYYYNNNNYYY YYYYYYNNYYYY). 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'[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Hugo Kornelis]

Re: Update statement

On 28 Apr 2006 00:10:17 -0700, Munno wrote:
[color=blue]
>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.
>(YYYYYYYNNNNYY YYYYYYYNNYYYY). 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,[/color]

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]

Re: Update statement

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