Post reply

update using subquery

  • May 6, 2004 at 2:22 pm

    #85325

    hi,

    i am trying to update one table column with other table column

    UPDATE MET SET abc=(SELECT xyz FROM EQ WHERE EQ.EQNO=MET.EQNO)

    WHERE DESCRIPTION='AAA'

    when i ran this query iam getting following error

    Server: Msg 515, Level 16, State 2, Line 1

    Cannot insert the value NULL into column 'ABC', table 'TEST.dbo.MET'; column does not allow nulls. UPDATE fails.

    The statement has been terminated.

    but there is no NULL value. when i used cursor it worked well. then why subquery is giving error.

     

    Thanks

     

  • May 6, 2004 at 2:33 pm

    #505555

    Try this

     

    UPDATE MET SET abc = EQ.xyz

    FROM

    MET

    INNER JOIN

    EQ

    ON

    EQ.EQNO = MET.EQNO

    WHERE MET.[DESCRIPTION] = 'AAA'

  • May 6, 2004 at 3:57 pm

    #505565

    hi Antares686

    thanks for your reply. its worked well. could you explain me what is wrong in my query.

     

    Thanks

  • May 6, 2004 at 4:08 pm

    #505567

    What most likely happend is this

    In MET you have an item where DESCRIPTION='AAA' but the MET.EQNO does not have a partner value in the EQ table and since no value returned from the subquery NULL is automatically assumed as the value becuase there is no known value.

Viewing 4 posts - 1 through 3 (of 3 total)

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