Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Location
    Athens,GR
    Posts
    16

    Red face Unanswered: i cannot solve it

    i have 2 tables.the first contains lastname,phone1,adress,etc...the second contains firstname,phone1. I want to insert the firstname from the second table into the first table where phone1 one is identical.
    thank you..

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Do you want to insert a new row or update an existing row?
    Last edited by r123456; 12-23-03 at 04:34.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2003
    Location
    Athens,GR
    Posts
    16
    Originally posted by r123456
    Do you want to insert a new row or update an existing row?
    thank you for your reply
    i want to update the row.
    Your statements add and unfortunatelly dont make all the comparisons,though the compared fields are of the same data type

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    update table2
    set firstname =
    (select firstname
    from table1 t1
    where key = t1.key);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Dec 2003
    Location
    Athens,GR
    Posts
    16
    Originally posted by r123456
    update table2
    set firstname =
    (select firstname
    from table1 t1
    where key = t1.key);
    ANALYSER'S MSG
    Server: Msg 512, Level 16, State 1, Line 1
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.

    thank you

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    You need to join the tables on a candidate key. If you replaced key with 'phone' then the query is returning multiple phone numbers meaning a phone number is not unique. Join on the primary key as this will ensure only a single row is returned.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Dec 2003
    Location
    Athens,GR
    Posts
    16
    Originally posted by r123456
    You need to join the tables on a candidate key. If you replaced key with 'phone' then the query is returning multiple phone numbers meaning a phone number is not unique. Join on the primary key as this will ensure only a single row is returned.
    i set [phone] as primary key and is accepted.
    Although the querry doesnt write all the fields i selected in my table
    ..... it seems it doesnt make the comparison.

    thank you.

  8. #8
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select *
    from table1 t1
    INNER JOIN
    table2 t2 ON
    t1.phone = t2.phone;

    If this does not return macthing rows then the data types of phone in both tables are not identical;
    Last edited by r123456; 12-23-03 at 12:36.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  9. #9
    Join Date
    Dec 2003
    Location
    Athens,GR
    Posts
    16
    Originally posted by r123456
    Select *
    from table1 t1
    INNER JOIN
    table2 t2 ON
    t1.phone = t2.phone;

    If this does not return macthing rows then the data types of phone in both tables are not identical;
    thanks a lot for your help

Posting Permissions

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