Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Unanswered: update statement

    I am migrating data from a column in one table to a column in another table. Is there a way to write a simple update statement to do this? There is a foreign key relationship between the two tables.

    I have a PERSON table and a USERS table.
    I need to move the column values for USERID from USERS to a new USERID column on PERSON.
    There is a foreign key from USERS to PERSON on PERSON_ID.

    I need an update statement that does something like the following:

    update person p
    set p.userid = users.userid
    where p.person_id = users.person_id

    Any suggestions?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    update person p
    set p.userid = ( select users.userid from users where p.person_id = users.person_id);
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    16
    The sql below gives me the error that single row subquery returns multiple rows.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Simple solution to this could be
    Code:
    UPDATE person p
       SET p.userid = (SELECT DISTINCT u.userid
                         FROM users u
                        WHERE p.person_id = u.person_id);
    But, if you have multiple distinct "userid" values for given "person_id", it won't work. You'll have to decide which one to use (for example, selecting MAX(userid) or something like that). Or broaden WHERE condition, for example
    Code:
    UPDATE person p
       SET p.userid =
              (SELECT u.userid
                 FROM users u
                WHERE p.person_id = u.person_id
                  AND u.start_date = (SELECT MAX (u1.start_date)
                                        FROM users u1
                                       WHERE u1.person_id = u.person_id));

Posting Permissions

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