CODEWITHSUNDEEP
sqlt-sql
Mark
Mark

Reputation: 25

Using subquery in update statement

I have 2 tables A and B. I need to run an update on table A but getting one value from one field in table B to be assigned to one field in table A,

The code as the following:

Update [A]    
    set A.Code = 10,
        A.Name = 'Test',
        A.Link = (Select Link from [B] where [B].ID = 10)    
    from [A]

The problem, the value in A.Link is always empty!!!!

Any idea what's wrong

Thanks,

Upvotes: 1

Views: 2300

Answers (3)

Tulip
Tulip

Reputation: 21

using joins can be more efficient,

Update [A]
set A.Code = 10,
    A.Name = 'Test',
    A.Link = B.Link    
from [A]
join [B] on [A].ID = [B].ID
where [B].ID=10;

Hope this works!

Upvotes: 1

iivel
iivel

Reputation: 2576

Does A.Code = B.ID? If so ...

UPDATE A
  SET A.LINK = B.LINK
     ,A.NAME = 'TEST'
FROM
  TABLE_A A
  INNER JOIN TABLE_B B
    ON A.CODE = B.ID

Upvotes: 4

p.campbell
p.campbell

Reputation: 100567

Try this:

 Update [A]
 set A.Code = 10, 
     A.Name = 'Test', 
     A.Link = ISNULL((Select Link from [B] where [B].ID = 10),'it was null')
 from [A]

 SELECT * FROM [A] WHERE Name = 'Test'

Does is have the custom it was null message in the Link column?

Upvotes: 0

Related Questions