Re: Update statement

Munno (patelroshan@xxxxxxxxx) 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, esquel@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: sqlserver 2000 new subnet mask login problem
    ... It seems that when you change the mask it stops you seeing the remote ... but at a guess you are either changing the subnet in the wrong location ... e.g in a VPN configuration rather than the local network or the mask is ... > Microsoft SQL Server Login: ...
    (microsoft.public.sqlserver.server)
  • Update statement
    ... I am not so proficient in SQL and seek your help. ... I have a column by the name of Mask in a table, ... How would my update statement look like? ... select user, substringAuthorisation from users where type ...
    (comp.databases.ms-sqlserver)
  • Update Statement
    ... I am not so proficient in SQL and seek your help. ... I have a column by the name of Mask in a table, ... How would my update statement look like? ... select user, substringAuthorisation from users where type ...
    (comp.databases.ms-sqlserver)