Updating with a join (again) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updating with a join (again)

Author

Topic

outspaced
Starting Member

21 Posts

Posted - 2007-02-23 : 07:59:44

3 tables - products, titles and images

products includes titleid and imageid.
products.titleid = titles.titleid
products.imageid = images.imageid

In the table images I have the fields imagesource and imageid - the imagesource field includes the cataloguenumber of the title I want to match the image to.

I want to update the table products so that the field products.imageid matches images.imageid for the entry where images.imagesource is like titles.cataloguenumber. Any suggestions?

Cheers,
Alex

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-02-23 : 08:17:31

[code]UPDATE p
SET p.ImageID = i.ImageID
FROM dbo.Products AS p
JOIN dbo.Titles AS t
ON p.TitleID = t.TitleID
JOIN dbo.Images AS i
ON i.ImageSource LIKE '%' + t.CatalogueNumber + '%' [/code]

Mark

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-23 : 08:17:36

this ?


update p
set    imageid = i.imageid
from   products p 
       inner join titles t on p.titleid = t.titleid
       inner join images i on i.imagesource = t.cataloguenumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources