Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: 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 ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    update [Users] 
       set id_Localisation  = t2.id_Localisation
      from [Users] t1
    inner
      join Localisation t2
        on t1.Localisation  = t2.Localisation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    it works perfectly

    thanks a lot

    do you thing i have to add a contrainst to this new column ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it would be a good idea to declare Users.id_Localisation as a foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    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

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anselme
    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anselme
    but 5 tables are using this id_Localisation, can i add a FK to each one ?
    yes .
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat
    You can use ON DELETE SET NULL for that purpose
    unfortunately, not in SQL Server 2000, only in SQL Server 2005
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by r937
    unfortunately, not in SQL Server 2000, only in SQL Server 2005
    Ah, right. I checked the wrong manual

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i wouldn't exactly call it wrong -- i'm sure it's the right one for SQL Server 2005!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2005
    Posts
    266
    thank you

    this application must work on 2000 and 2005

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •