Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2011
    Location
    Kuala Lumpur,Malaysia
    Posts
    9

    Unanswered: how to insert data without overwrite old data in the same table

    hi anyone here know how to insert data without overwrite the existing or current data in a table.

    i have a table which is store a health information, this health information will change week by week, so i don't want to overwrite information and i want keep as a history info

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by annaright View Post
    hi anyone here know how to insert data without overwrite the existing or current data in a table.
    use a UNIQUE key to prevent duplication
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Location
    Kuala Lumpur,Malaysia
    Posts
    9
    Quote Originally Posted by r937 View Post
    use a UNIQUE key to prevent duplication
    sorry maybe my question a bit confusing, its not INSERT but UPDATE....i want to update a table which is already have a data in the same time i don't want to overwrite the existing data. sorry my english really bad

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you want to update, but don't want to overwrite existing data?

    that doesn't make sense

    please give a specific example of a row that already exists, and the update that you would like to make to it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2011
    Location
    Kuala Lumpur,Malaysia
    Posts
    9
    Quote Originally Posted by r937 View Post
    you want to update, but don't want to overwrite existing data?

    that doesn't make sense

    please give a specific example of a row that already exists, and the update that you would like to make to it
    example:

    Table person:

    P_Id LastName FirstName Address City
    1 John Mayer Street 1 London
    2 Ray Caroline Street 2 Leeds

    UPDATE Persons
    SET Address='Street3', City='Manchester'
    WHERE LastName='Ray' AND FirstName='Caroline'

    The "Persons" table will now look like this:

    P_Id LastName FirstName Address City
    1 John Mayer Street 1 London
    2 Ray Caroline Street 3 Manchester

    but I still want to keep the previous data before the update as a history

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by annaright View Post
    but I still want to keep the previous data before the update as a history
    aha!!

    okay, where would you like to keep it?

    obviously not in this table, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2011
    Location
    Kuala Lumpur,Malaysia
    Posts
    9
    Quote Originally Posted by r937 View Post
    aha!!

    okay, where would you like to keep it?

    obviously not in this table, right?
    yup not in this table...do you have any idea, how to insert to new table in the same time update the data in the existing table, probably this is the solution.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by annaright View Post
    ...do you have any idea, how to insert to new table in the same time update the data in the existing table
    an INSERT statement which uses the SELECT option (rather than the VALUES option), followed by an UPDATE statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2007
    Posts
    197
    Can we not use trigger before insert

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ankur02018 View Post
    Can we not use trigger before insert
    trigger before update, i should think
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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