Update based on another table result

Question

I want to update a temp table based on another table result set.

Any suggestions. The select query works independently. But I am thinking to integrate with the update statement.

UPDATE #person_membership_promo_ext 
SET note_about=
(
    select note_text
    FROM note nt 
    INNER JOIN #person_membership_promo_ext per
      ON per.person_id=nt.main_ref_id
        and per.membership_type='P'
        and note_id=(select MAX(note_id)from note nt_1
    where nt_1.main_ref_id=per.person_id)
)

Rewrote the query as below and it is working. – user3120927 Jan 16 '14 at 00:00 — user3120927, Jan 16 '14 at 00:00

score 0 · Answer 1 · answered Jan 16 '14 at 00:00

0

UPDATE per
SET note_about=nt.note_text
FROM note nt 
INNER JOIN #person_membership_promo_ext per
  ON per.person_id=nt.main_ref_id
    and per.membership_type='P'
    and note_id=(select MAX(note_id)from note nt_1
where nt_1.main_ref_id=per.person_id)

answered Jan 16 '14 at 00:00

user172839

1,035
1
10
19

score 0 · Answer 2 · answered Jan 16 '14 at 00:01

You can use joins in update statements

UPDATE per
SET note_about = nt.note_text
FROM #person_membership_promo_ext per
INNER JOIN note nt
  ON per.person_id=nt.main_ref_id
    and per.membership_type='P'
    and note_id = (
        select MAX(note_id) 
        from note nt_1
        where nt_1.main_ref_id = per.person_id
   )

score 0 · Answer 3 · answered Jan 16 '14 at 03:41

Here you go,

Update per
set per.note_about = nt.note_text
FROM note nt 
INNER JOIN #person_membership_promo_ext per
ON per.person_id=nt.main_ref_id
and per.membership_type='P'
and note_id=(select MAX(note_id)from note nt_1
where nt_1.main_ref_id=per.person_id)

I hope this will help!

Update based on another table result

3 Answers3

Linked