Update column from inner join

Question

I have a table of posts from a forum, basically there is one row for the posting of a pdf file , then there is another row for the posting of the image url that goes with the pdf. So I took all the rows out of posts table and inserted them into Item table if they are a pdf. Now I have a column in Item table that I want to get filled with the row in the posts table that has the image basically I want

UPDATE Item i Set i.ImageUrl = p.guid
JOIN posts p ON i.old_id = p.parent_post
where p.PostType = 'image'

I have not been able to find a good example anywhere that is an Update that uses a join and has a WHERE clause , does anyone have any idea what I am doing wrong?

score 3 · Accepted Answer · answered Nov 27 '14 at 05:10

3

The correct syntax in SQL Server looks more like this:

UPDATE i
    SET ImageUrl = p.guid
    FROM Item i JOIN
         posts p
         ON i.old_id = p.parent_post
    WHERE p.PostType = 'image';

answered Nov 27 '14 at 05:10

Gordon Linoff

1,242,037
58
646
786

Update column from inner join

1 Answers1