Help with SELECT/UPDATE - 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)

Help with SELECT/UPDATE

Author

Topic

masterslave
Starting Member

22 Posts

Posted - 2008-01-11 : 21:00:13

Hi!

I've got a SELECT query which I need to convert to UPDATE statement.
I'm need to update a set of data which is select below in table CatalogueItem for field BaseProductId with value selected in [Base_2008]. And I'm not sure how to do it! CatItem2007 and CatItem2008 are just two sub-sets of data based on date ranges.

SELECT
CatItem2007.BaseProductId AS [Base_2007],
CatItem2008.BaseProductId AS [Base_2008
FROM
CatalogueItem CatItem2007
INNER JOIN
CatalogueProduct CatProd2007 ON (CatProd2007.CatalogueProductId = CatItem2007.CatalogueProductId)
INNER JOIN
BaseProduct ON (CatItem2007.BaseProductId = BaseProduct.BaseProductId)
INNER JOIN
CatalogueItem CatItem2008 ON (CatItem2007.Item = CatItem2008.Item)
AND ((CatItem2008.DateActive <= '2008-01-01') AND (CatItem2008.DateArchived IS NULL OR CatItem2008.DateArchived > '2008-12-31' ))
AND CatItem2008.IsNew = 0
AND CatItem2008.IsDeleted = 0
AND CatItem2007.SourceCode = 'ITS'
AND CatItem2008.SourceCode = 'HEW'
AND (CatItem2007.BaseProductId <> CatItem2008.BaseProductId)
INNER JOIN
CatalogueProduct CatProd2008 ON (CatProd2008.CatalogueProductId = CatItem2008.CatalogueProductId)
WHERE
((CatItem2007.DateActive <= dbo.Date(2007, 1,1)) AND (CatItem2007.DateArchived IS NULL OR CatItem2007.DateArchived > dbo.Date(2007, 12, 31) ))
AND
CatItem2007.IsDeleted = 0
ORDER BY
CatItem2007.Item

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-12 : 00:00:36

WHich is table to be updated? what is common field based on which update is to be done? Can you expelain this in detail with some sample data too?

masterslave
Starting Member

22 Posts

Posted - 2008-01-12 : 01:07:04

Table that needs to be updated is CatalogueItem, and the field is BaseProductId.

The thing BaseProduct is used for in this case is to tie together catalogue items records from various years. For example, we print a catalogue last year with a particular pencil whose catalogue code is ITS-123, in this year's catalogue the code has changed to ITS-1234 but because it's tied to one base product we can get history of this item in the catalogue.

What happened here was that in the system a number of products got attached to wrong base products, so in 2007 the catalogue item ITS-999 was connected to base product PEN-999, but in 2008 ITS-999 record is connected to ITS-999. What I need is to connect 2007 catalogue item record to ITS-999 instead of PEN-999. I hope I'm explaining it OK...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-12 : 01:45:47

Try like this:-

UPDATE CatItem2007
SET CatItem2007.BaseProductId=CatItem2008.BaseProductId 
FROM 
CatalogueItem CatItem2007
INNER JOIN
CatalogueProduct CatProd2007 ON (CatProd2007.CatalogueProductId = CatItem2007.CatalogueProductId) 
INNER JOIN
BaseProduct ON (CatItem2007.BaseProductId = BaseProduct.BaseProductId) 
INNER JOIN
CatalogueItem CatItem2008 ON (CatItem2007.Item = CatItem2008.Item)
AND ((CatItem2008.DateActive <= '2008-01-01') AND (CatItem2008.DateArchived IS NULL OR CatItem2008.DateArchived > '2008-12-31' )) 
AND CatItem2008.IsNew = 0
AND CatItem2008.IsDeleted = 0
AND CatItem2007.SourceCode = 'ITS'
AND CatItem2008.SourceCode = 'HEW'
AND (CatItem2007.BaseProductId <> CatItem2008.BaseProductId)
INNER JOIN
CatalogueProduct CatProd2008 ON (CatProd2008.CatalogueProductId = CatItem2008.CatalogueProductId) 
WHERE
((CatItem2007.DateActive <= dbo.Date(2007, 1,1)) AND (CatItem2007.DateArchived IS NULL OR CatItem2007.DateArchived > dbo.Date(2007, 12, 31) )) 
AND 
CatItem2007.IsDeleted = 0

masterslave
Starting Member

22 Posts

Posted - 2008-01-12 : 03:07:19

Thanks very much, visakh16, it worked!!
I couldn't get my head around it... Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources