SQL STATEMENT TO CHANGE COLUMN VALUE!

This table is actually an index of a table.

I get this error when trying to so the command

Incorrect syntax near the keyword 'table'.

Update table PK_members set <column_name> = 'true' where <column_name> = 'false';
 
 
How can I just create and index that will set the column Unique key to true

or if you mean alter a table to add a constraint?

ALTER TABLE Names
ADD CONSTRAINT UniqueValues
UNIQUE (names);

that should be

ALTER TABLE PK_members
ADD CONSTRAINT UniqueValues
UNIQUE (names);

Did you want to modify the table definition or update some values in the table column?

In my query, did you run it with the right column name, I did not get your column name. You can find it by running:

desc PK_members

You are saying that PK_members is a index to some table. Are you intending to update the index or the table field. You cannot change the value of a field in index. It will be based on the value in the table.

Let me clarify

I need to alter and INDEX called PK_members

there is a cloumn called Unique key that is currently set to false.

I need to cahnge it to true

how can I make the index a unique key???

Hi,

You have posted this in the mysql and mssql areas - which db are you using?

Additionally - if this is the primary key you can not set it to be non unique.

If this is a key and not a primary key - it however is unique you could do

in mysql - to change from a non unique key to a unique one you need to drop the index - then add it again as "unique" including the definition again for the keys involved.

alter table `schemaName`.`tableName` drop key `PK_members`, add Unique  `PK_members` ( `Column1`,`Column2` )

You can create a UNIQUE index by using
CREATE UNIQUE <CLUSTERED/NONCLUSTERED> INDEX index_name
ON table_name.col_name

Windows SQL server

It is on godaddy.com

it defaults the Unique key column to FALSE and there is no way to change it without making a statement.

Same principle applies,

Firstly drop the index - then run the command that Gokulm gave.

Hi judsonmusic,


SET NOCOUNT OFF
USE MyDatabase
IF EXISTS (SELECT name FROM sysindexes
      WHERE name = 'PK_members')
   DROP INDEX MyTable.PK_members
GO
USE MyDatabase
CREATE UNIQUE INDEX PK_members
   ON MyTable (MyColumn)
GO

Kosturdur

If you want to make it as a unique key ,
you must clean up the data first from any duplicate , then you can alter the table successfully

DROP INDEX PK_members
CREATE UNIQUE INDEX PK_MEMBERS ON MyTable(MyColumn)

==================

You will need to replace MyTable with the name of the table the index is on, and MyColumn with the name(s) of the column(s) the index is on.

If you have Enterprise Manager, you can open the index (by design Table, then Indexes/Keys), check the checkbox "Create UNIQUE" for your index, save, and it will become unique.

sorry

Hi judsonmusic,

*******************
SET NOCOUNT OFF
USE MyDatabase
IF EXISTS (SELECT name FROM sysindexes
      WHERE name = 'MyIndexName')
   DROP INDEX MyTable.MyIndexName
GO
USE MyDatabase
CREATE UNIQUE INDEX MyIndexName
   ON MyTable (MyColumn)
GO
************************
now ok

Kosturdur

I deleted the indexmanually and did this:


CREATE UNIQUE INDEX PK_members
 ON members (ID)
GO

The problem is that there are 2 columns in the index, one called unique, which is set to true, and one called unique key, which is set to false. I need to change unique key to true!

Thanks

i guess a better question is why?
why do you need one set but not the other?

these are the columns:

Index /Columns /Primary /Unique /Clustered /Ignore dup. keys /Unique key /Stats /no recomp. /Fill factor /File group/PK_members id

they are set to(in order)

  True  /True  /True  /False /False  /False  /0  /PRIMARY

I need to change the Unique key column to TRUE

Update PK_members set Unique key = 'true' where Unique key = 'false';

Incorrect syntax near the keyword 'Unique'.

Update PK_members set Unique key = 'true' where Unique key = 'false';

What's your Column's name? I thing it is "UniqueKey" or "Unique_Key" please chance  the "Unique key" part whit the name of your Column.

Update PK_members set Unique key = 'true' where Unique key = 'false';

For example;

Update PK_members set Uniquekey = 'true' where Uniquekey = 'false';


with no spaces(blanks)

ok try this

Update PK_members set PK_members.[Unique key] = 'true' where PK_members.[Unique key] = 'false';

Kosturdur

If you have space in the column name you might try using
Update PK_members set [Unique key] = 'true' where [Unique key] = 'false'

Since you are talking about an index an not about a table I don't believe that Kosturdur's approach will work.

Have you considered making this field the primary key? does this change the Unique Key value to true? alternatively make it a foreign key?

But again - I ask why do you need this? if you are hoping to achieve something there may be a better approach.

I am using dreamweaver to develop a update record function and when I create a recordset it says please choose a table with a unique key.

i'd suggest that you add this as a primary key - not just a simple index

Defining a Primary Key
Define a primary key to enforce uniqueness for values entered in specified columns that do not allow nulls. If you define a primary key for a table in your database, you can relate that table to other tables, thus reducing the need for redundant data. A table can have only one primary key.  

To define a primary key

In Enterprise manager, in your database diagram or Table Designer, click the row selector for the database column you want to define as the primary key. If you want to select multiple columns, hold down the CTRL key while you click the row selectors for the other columns.

Right-click the row selector for the column and select Set Primary Key. A primary key index, named "PK_" followed by the table name, is automatically created; you can find it on the Indexes/Keys tab of the property pages.
Warning   If you want to redefine the primary key, any relationships to the existing primary key must be deleted before the new primary key can be created. A message will warn you that existing relationships will be automatically deleted as part of this process.

A primary key column is identified by a primary key symbol  in its row selector.

If a primary key consists of more than one column, duplicate values are allowed in one column, but each combination of values from all the columns in the primary key must be unique.

If you define a compound key, the order of columns in the primary key matches the order of columns as shown in the table in your diagram. However, you can change the order of columns after the primary key is created.

Kosturdur

vadimrapp1:

I accepted your answer due to the fact that you are correct, I basically have limited control over this situation.

I wound up deleteing everything and starting over. I already had the index set up and column ID as primary key. For some reason, Dreamweaver kept giving me an error about it saying that the recordset could not be created without a primary key on the table.

I uninstalled and re-installed dreamweaver and now it works. I want to aplogize to everyone for the wate of time and praise you for your answers.

Thanks

Judson   www.judsonmusic.com

In that case, it might be more productive to call the godaddy service provider as it's not really and SQL Server question.