sqlt-sql

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,


Solution

  • 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