Updating Multiple row subquery ..!! Help ..

  • Hi,

    I have a subquery which would return multiple records.

    I need to update another table with these multiple records.

    Say table has Name, age, birthdate etc .. as columns

    and .. table2 has emp_name and emp_age

    my sub query is like ..

    select a.emp_name from table2 a, table b where

    a.emp_age = b.age

    which will return multiple rows..

    For this same where clause i need to update table.Name

    with the table2.emp_name obtained from the above mentioned query ..

    i tired some queries like this..

    UPDATE table t1

    SET (Name) = (

    SELECT emp_name

    FROM table2 t2

    WHERE t2.emp_age = t1.age );

    but didnt work..!!

    Please help..

  • manju_gallivant (3/6/2009)


    Hi,

    I have a subquery which would return multiple records.

    I need to update another table with these multiple records.

    Say table has Name, age, birthdate etc .. as columns

    and .. table2 has emp_name and emp_age

    my sub query is like ..

    select a.emp_name from table2 a, table b where

    a.emp_age = b.age

    which will return multiple rows..

    For this same where clause i need to update table.Name

    with the table2.emp_name obtained from the above mentioned query ..

    i tired some queries like this..

    UPDATE table t1

    SET (Name) = (

    SELECT emp_name

    FROM table2 t2

    WHERE t2.emp_age = t1.age );

    but didnt work..!!

    Please help..

    Seems simple update and no need subquery!

    Try this one:

    UPDATE TABLE1

    SET TABLE.NAME = TABEL2.EMP_NAME

    FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.AGE = TABLE2.EMP_AGE

    Hope it helps what do you want!

    Anyway... you should post the structure of your tables and some sample data to be clear you problem and for the others who post the solution for you to be more easy writing the code and to help you faster!

    Post the tables structure, add some sample data then tell what do you want to do, how to you want to see that data!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Other thing I like add apart from what Dugi has said -

    Use ANSI JOINS instead of old where join syantax. You can still get the corect result with old join syntax for inner joins, but you won't able to do a OuTER JOINS in sql server 2005 or later versions.

    John Smith

  • i thought it to keep it simple..

    Let me give a full description..

    I have two tables.. TIDLGGLS and TIDLRREP.

    Among 29 columns in TIDLGGLS .. Few of concern..

    TRANSLATED_TRM_160 VARCHAR2(3000 BYTE) NOT NULL,

    TRANS_TERM_SIZE NUMBER(3) DEFAULT 0 NOT NULL,

    REPOSITORY_KEY CHAR(10 BYTE) NOT NULL

    BASE_LANGUAGE_TERM VARCHAR2(3000 BYTE) NOT NULL,

    And in TIDLRREP

    REPOSITORY_KEY CHAR(10 BYTE) NOT NULL,

    BASE_LANGUAGE_TERM CHAR(79 BYTE) NOT NULL,

    TRANSLATED_TERM_79 CHAR(79 BYTE) NOT NULL,

    TRANS_TERM_SIZE NUMBER(3) NOT NULL,

    MAX_TERM_SIZE NUMBER(3) NOT NULL,

    This is the query i used for updating TIDLRREP.

    UPDATE TIDLRREP t

    SET (t.translated_term_79) =

    ( SELECT a.TRANSLATED_TRM_160

    FROM TIDLGGLS a

    WHERE upper(a.base_language_term) = upper(t.base_language_term) AND

    t.MAX_TERM_SIZE >= a.TRANS_TERM_SIZE)

    this works fine when the no of records it handles in less..

    We are handling some 80k records.

    After few records it says cannot insert null to translated_term_79 ..

    There are no null values in TRANSLATED_TRM_160 also..

    Please help..

  • If you were to write a SELECT against these two tables which returned just the rows you want to update from the target table, along with the target column and also the column from the source table providing the new value, then you would be almost there. Like this:

    -- check first using SELECT

    SELECT t1.age, t1.[name], t2.emp_name

    FROM

    t1

    INNER JOIN table2 t2

    ON t2.emp_age = t1.age

    -- SELECT returns correct rows & values, so

    -- ok to run UPDATE

    UPDATE t1 SET [name] = t2.emp_name

    FROM

    t1

    INNER JOIN table2 t2

    ON t2.emp_age = t1.age

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi Hope it helps..

    CREATE TABLE TBL_NAMES(

    NAMES VARCHAR(50),

    AGE INT,

    BIRTH_DATE DATETIME

    )

    CREATE TABLE EMPLOYEE(

    EMP_NAME VARCHAR(50),

    EMP_AGE INT

    )

    INSERT INTO TBL_NAMES VALUES('Senthil',21,GETDATE());

    INSERT INTO TBL_NAMES VALUES('Kumar',23,GETDATE());

    INSERT INTO TBL_NAMES VALUES('Mathan',29,GETDATE());

    INSERT INTO TBL_NAMES VALUES('John',25,GETDATE());

    INSERT INTO EMPLOYEE VALUES('Senthil Nathan',21)

    INSERT INTO EMPLOYEE VALUES('Kumara Raghavan',23)

    INSERT INTO EMPLOYEE VALUES('Vel',63)

    SELECT * FROM TBL_NAMES

    SELECT * FROM EMPLOYEE

    UPDATE TBL_NAMES SET TBL_NAMES.NAMES = EMPLOYEE.EMP_NAME

    FROM TBL_NAMES JOIN EMPLOYEE ON EMPLOYEE.EMP_AGE = TBL_NAMES.AGE

    Thanks,

    nathan

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

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