Update a Column after a SQL Join

Question

I am using the Select and Right Join SQL functions to call all the data into one table. Data being called will only populate if ans_primary = '1' on the Assignment table. If asn_primary = '1' then it will join all the below columns on att_id = att_id for both tables.

The two tables and the columns used are below:

Assignment (att_id, itm_id, asn_primary)

Attachment (att_id, att_name, att_type, att_path, att_path2, att_path3)

select assignment.itm_id, attachment.att_name, attachment.att_type, attachment.att_path,    attachment.att_path2, attachment.att_path3
    from assignment
    right join attachment
    on assignment.att_id=attachment.att_id
    where assignment.asn_primary = '1'

I need to be able to update all the fields in the att_name column after the call has been made. I am not sure how to update columns after a Join call is used.

The SQL I need to run after the info has been called/joined is:

Update attachment
set att_name = att_name + '.tif'
where att_path3 like '%.tif%'

Answer 1

The syntax for join in an update in SQL Server is:

Update att
    set att_name = att_name + '.tif'
    from assignment a join
         attachment att
         on a.att_id = att.att_id
    where a.asn_primary = '1' and
          att.att_path3 like '%.tif%';

The right outer join doesn't seem appropriate, because you are filtering on a field from assignment.