Post reply

How to Update records of one table by selecting more than one rows from another table

  • December 16, 2011 at 3:54 am

    #247825

    Update table1

    set table1.foreginer='yes'

    where table1.PersonalID=(select table2.PersonalID

    from table2

    where table2.Nationality!='American')

    I am applying following query to update table 1, All fields are varchar whereas PersonalID is primary key of table1 and passed as foriegn key in table2

    but it gives error

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Because this select satatement return more than one records. how can update all those records in table 1 which have not american nationality in table2

  • December 16, 2011 at 4:03 am

    #1422373

    Thanks Everyone

    I got solution here

    http://geekswithblogs.net/faizanahmad/archive/2009/01/05/join-in-sql-update--statement.aspx

  • December 16, 2011 at 4:05 am

    #1422375

    UPDATE A

    SET foreginer='yes'

    FROM table1 A

    INNER JOIN table2 B

    ON A.PersdonalID = B.PersonalID

    WHERE B.Nationality!='American'

  • December 16, 2011 at 4:22 am

    #1422382

    Beware - this will bite you if any row you are updating (call it table1) has more than one match in the table you are updating from (table2). You have no way of knowing which matching row from table2 will be used to update the value in table1. Worse still, SQL Server does not issue any error or warning. I would advise you to use MERGE instead.

    John

  • December 16, 2011 at 5:06 am

    #1422397

    John Mitchell-245523 (12/16/2011)

    Beware - this will bite you if any row you are updating (call it table1) has more than one match in the table you are updating from (table2). You have no way of knowing which matching row from table2 will be used to update the value in table1. Worse still, SQL Server does not issue any error or warning. I would advise you to use MERGE instead.

    John

    Good point well made

  • December 16, 2011 at 5:10 am

    #1422401

    John Mitchell-245523 (12/16/2011)

    Beware - this will bite you if any row you are updating (call it table1) has more than one match in the table you are updating from (table2). You have no way of knowing which matching row from table2 will be used to update the value in table1. Worse still, SQL Server does not issue any error or warning. I would advise you to use MERGE instead.

    John

    Or IN:

    UPDATE table1 SET foreigner = 'yes'

    WHERE PersonalID IN (SELECT t2.PersonalID FROM table2 t2 WHERE t2.Nationality!='American')

    or EXISTS:

    UPDATE table1 SET foreigner = 'yes'

    WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t2.PersonalID = table1.PersonalID AND t2.Nationality!='American')

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • December 16, 2011 at 6:26 am

    #1422449

    Actually, yes, my caveat doesn't apply in this instance, since all we're doing is applying an existence test. It's where you're taking a value from one table to update another that you have to be careful.

    John

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply