Alter table new column and update

Hi

for MS SQL 2000/2005

I am having a table (an old database, not mine) with char value for the column [localisation]

Users
[name] [nvarchar] (100) NOT NULL ,
[localisation] [nvarchar] (100)NULL

Now i have created a table [Localisation]

Localisation
[id_Localisation] [int] NOT NULL,
[localisation] [nvarchar] (100) NOT NULL

I am adding a new column to Users

ALTER TABLE [Users] ADD
[id_Localisation] int NULL

and I want to update the Column [Users].[id_Localisation] before to drop the column [Users].[Localisation]

something like

UPDATE [Users] SET id_Localisation = (SELECT Localisation.id_Localisation
FROM Localisation FULL OUTER JOIN
Users ON Localisation.Localisation = Users.Localisation)

Users.Localisation can have a NULL value (then no id_localisation return)

but it doesnt work because it returns > 1 row

thank you

how can I do it ?update [Users]
set id_Localisation = t2.id_Localisation
from [Users] t1
inner
join Localisation t2
on t1.Localisation = t2.Localisation|||it works perfectly

thanks a lot

do you thing i have to add a contrainst to this new column ?|||it would be a good idea to declare Users.id_Localisation as a foreign key|||but 5 tables are using this id_Localisation, can i add a FK to each one ?
FK_FK_Users_Localisation
FK_job_Localisation
FK_groups_Localisation
.....

if so

5 times (for each tables)

ALTER TABLE [Users] ADD
id_Localisation int NULL

ALTER TABLE [Users] WITH NOCHECK ADD
CONSTRAINT [FK_Users_Localisation] FOREIGN KEY
(
[id_Localisation]
) REFERENCES [Localisation] (
[id_Localisation]
)

I dont want to apply ON DELETE CASCADE , but to give a Id_localisation = 0 or NULL if a Localisation is deleted, how can i do it
??

thanks again for helping|||I dont want to apply ON DELETE CASCADE, but to give a Id_localisation = 0 or NULL if a Localisation is deleted
You can use ON DELETE SET NULL for that purpose|||but 5 tables are using this id_Localisation, can i add a FK to each one ?yes . ;)|||You can use ON DELETE SET NULL for that purposeunfortunately, not in SQL Server 2000, only in SQL Server 2005|||unfortunately, not in SQL Server 2000, only in SQL Server 2005Ah, right. I checked the wrong manual ;)|||well, i wouldn't exactly call it wrong -- i'm sure it's the right one for SQL Server 2005!!|||thank you

this application must work on 2000 and 2005