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%'

score 0 · Answer 1 · answered Jan 22 '14 at 19:54

0

What you're looking for is referred to as a side-effecting query. You'll want to follow the basic structure as below. Be careful of the results. If a row shows up three times, it will be updated three times. This can significantly affect server performance and the resulting logs.

I usually imbed a 'SELECT *' to allow me to view the results prior to executing any updates.

UPDATE T1
SET T1.Column1 = 'New Data'
--SELECT *
FROM dbo.Table1 AS T1
INNER JOIN dbo.Table2 AS T2 ON T2.Id = T1.Id
WHERE T1.Column1 <> 'New Data';

answered Jan 22 '14 at 19:54

jtimperley

2,494
13
11

A row will only be updated once. If there are many possible joining ones an arbitrary source row will be used. – Martin Smith Jan 22 '14 at 19:56
Easy enough to verify against a database in full recovery mode. Update every record in a table with 1 million records both ways. You should see that the resulting logs are three times the size of the single update. – jtimperley Jan 22 '14 at 20:11
This worked great just like Gordon's response below. Thank you very much for the feedback @jtimperley! – Matt Weick Jan 22 '14 at 20:17
@jtimperley Easy enough to verify by looking at the execution plan. You'll see a single row is chosen. Probably with an `any` aggregate. Also you can set up a test where `a = a + 1` and notice the values are only incremented by one regardless of how many joining rows match. – Martin Smith Jan 22 '14 at 20:23
@martin-smith Execution plans provide insight, not deterministic results. I am sure there are many scenarios where SQL Server is smart enough to limit the final set to one value. Either way if you have Table1 mapped to three unique rows from Table2 you'll still receive random results regardless of how many times SQL Server performs the update. – jtimperley Jan 22 '14 at 20:33

score 0 · Accepted Answer · answered Jan 22 '14 at 19:54

0

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.

answered Jan 22 '14 at 19:54

Gordon Linoff

1,242,037
58
646
786

This worked like a charm. I will note for future reference going forward how it's structured. Thanks Gordon! – Matt Weick Jan 22 '14 at 20:15

score 0 · Answer 3 · answered Jan 22 '14 at 19:55

0

UPDATE assignment
right join attachment
on assignment.att_id=attachment.att_id
SET att_name = att_name + '.tif'
where assignment.asn_primary = '1'
AND att_path3 like '%.tif%'

answered Jan 22 '14 at 19:55

Jlil

170
1
7

Update a Column after a SQL Join

3 Answers3