update using subquery

Question

Post reply

update using subquery

ravi

SSC Veteran

Points: 257
More actions
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

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Try this

UPDATE MET SET abc = EQ.xyz

FROM

MET

INNER JOIN

EQ

ON

EQ.EQNO = MET.EQNO

WHERE MET.[DESCRIPTION] = 'AAA'

ravi SSC Veteran Points: 257 More actions · Answer 2

hi Antares686

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

Thanks

Antares686 SSC Guru Points: 125444 More actions · Answer 3

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.