Results 1 to 5 of 5

Thread: Update Sql

  1. #1
    Join Date
    Jan 2004
    Posts
    25

    Unanswered: Update Sql

    CREATE TABLE NOASSETTAG
    (
    TaglessNo Integer NOT NULL,
    Serial_No Varchar(50),
    Model_Type Varchar(50),
    Product_Type Varchar(30) NOT NULL,
    HostName Varchar(25),
    IPAddress Varchar(20),
    Inv_Location Varchar(10) NOT NULL,
    Inv_Status Varchar(10) NOT NULL,
    Remarks Varchar(255),
    Date_of_Disposition Varchar(20),
    Asset_Owner Varchar(20),
    PRIMARY KEY (TaglessNO),
    FOREIGN KEY (Asset_Owner) REFERENCES USER(UserId) ON DELETE SET NULL,
    FOREIGN KEY (Inv_Location) REFERENCES LOCATION(Place) ON DELETE RESTRICT,
    FOREIGN KEY (Product_Type) REFERENCES PRODUCT(Prod_Name) ON DELETE RESTRICT
    );

    CREATE TABLE LOCATION
    (
    Place Varchar(10) NOT NULL,
    Room Varchar(25) NOT NULL,
    PRIMARY KEY(Place)

    );


    These are the 2 tables i have. How do i update the place value in the Location table and update all the values in the NoAssetTag.Inv_Location at the same time? Thanks in advance

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Update Sql

    Originally posted by Shinto
    CREATE TABLE NOASSETTAG
    (
    TaglessNo Integer NOT NULL,
    Serial_No Varchar(50),
    Model_Type Varchar(50),
    Product_Type Varchar(30) NOT NULL,
    HostName Varchar(25),
    IPAddress Varchar(20),
    Inv_Location Varchar(10) NOT NULL,
    Inv_Status Varchar(10) NOT NULL,
    Remarks Varchar(255),
    Date_of_Disposition Varchar(20),
    Asset_Owner Varchar(20),
    PRIMARY KEY (TaglessNO),
    FOREIGN KEY (Asset_Owner) REFERENCES USER(UserId) ON DELETE SET NULL,
    FOREIGN KEY (Inv_Location) REFERENCES LOCATION(Place) ON DELETE RESTRICT,
    FOREIGN KEY (Product_Type) REFERENCES PRODUCT(Prod_Name) ON DELETE RESTRICT
    );

    CREATE TABLE LOCATION
    (
    Place Varchar(10) NOT NULL,
    Room Varchar(25) NOT NULL,
    PRIMARY KEY(Place)

    );


    These are the 2 tables i have. How do i update the place value in the Location table and update all the values in the NoAssetTag.Inv_Location at the same time? Thanks in advance
    You would have to create a new LOCATION entry, update NOASSETTAG rows that refer to the old location, and then delete the original location. Alternatively, drop constraints and re-create them after update.

    PS. The fact that you need to update a primary key is a sign of a questionable physical model, in my opinion.

  3. #3
    Join Date
    Jan 2004
    Posts
    25
    i need to update the primary key Place because the place might change over time. BTW, how do you set the constraints on UPDATE?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    i need to update the primary key Place because the place might change over time.
    That's what I'm saying. If it changes it does not deserve to be a primary key.


    BTW, how do you set the constraints on UPDATE?
    Similarly to the way you create DELETE constraints: ON DELETE RESTRICT ON UPDATE RESTRICT. See SQL Reference for details.

  5. #5
    Join Date
    Jan 2004
    Posts
    25
    What do you mean by drop constraints and create them after update?

    How do i create a constraint after update?
    Last edited by Shinto; 03-23-04 at 00:21.

Posting Permissions

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