SQL Update Query with subquery

Hello experts!

I have a table which contains Items for two different sub companies, which all belong to a single master company. Table Product looks as such

Company ItemNumber NextCountDate
---------------------------------------------------------------
Co1           ItemABC      09/10/2012
Co1           ItemDEF      09/10/2012
Co2           ItemABC      01/01/1900
Co2           ItemDEF      01/01/1900

I am trying to update Co2's NextCountDate where Co1 ItemNumber == Co2 ItemNumber, but am having trouble doing this because they are in the same table.

Here is my query to get a match of Items which exist in both companies.
SELECT DISTINCT 
		  Product.Company
		, Product.ItemNumber
		, Product.NextCountDate
		, dt1.Company AS [dt1 Company]
		, dt1.ItemNumber AS [dt1 ItemNumber]
		, dt1.NextCountDate AS [dt1 NextCountDate]
FROM   Product INNER JOIN
      (SELECT DISTINCT 
				ItemNumber
				, Company
				, NextCountDate
       FROM   Product AS P) AS dt1 ON Product.ItemNumber = dt1.ItemNumber
WHERE (dt1.Company = 'Co2') AND (Product.Company = 'Co1') AND (NOT(Product.NextCountDate = CONVERT(DATETIME, '01/01/1900', 102)))

Open in new window


I am trying to use this query to run the update. I only want to update Co2 NextCountDate where ItemNumbers are equal, and Co1 NextCountDate does not equal the min date
I_sAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
This is where table aliases are extremely useful :


UPDATE P2
SET
    P2.NextCountDate = P1.NextCountDate
FROM dbo.Product P1
INNER JOIN dbo.Product P2 ON
    P2.Company = 'Co2' AND
    P2.ItemNumber = P1.ItemNumber
WHERE
    P1.Company = 'Co1' AND
    P1.NextCountDate <> '19000101'
0
 
sdstuberCommented:
what do you want to update nextcountdate to?  the same as Co1's date?
0
 
CluskittCommented:
The simplest way for you to build an update, is to create first a working select. That is, one that will return ALL the rows you want and ONLY the rows you want. For fields, select the pairs to be updated (SELECT OldField1, NewField1, OldField2, NewField2, etc..)
When you're happy with the results, replace the select part with:
UPDATE Table SET OldField1=NewField1, OldField2=NewField2, etc....
FROM Table (possibly with joins and where conditions)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
assuming you do want co2's nextcountdate to be set to co1's nextcountdate

then try this...
ee.txt
0
 
Jared_SCommented:
See if this does what you're after...

create table #mytable (company varchar (5), ItemNumber varchar (25), NextCountDate datetime)

insert into #mytable values
('Co1',           'ItemABC', '09/10/2012')
insert into #mytable values
('Co1',           'ItemDEF', '09/10/2012')
insert into #mytable values
('Co2',           'ItemABC', '01/01/1900')
insert into #mytable values
('Co2 ',          'ItemDEF', '01/01/1900')
;

select * from #mytable --before

update #mytable 
set #mytable.nextcountdate = (select max(m2.nextcountdate) from #mytable m2 
where m2.itemnumber = #mytable.itemnumber and m2.company = 'Co1' )
from #mytable 

select * from #mytable -- after

Open in new window

0
 
I_sAuthor Commented:
Simple and quick! Thanks worked perfectly.
0
 
CluskittCommented:
In a query like yours, you HAVE to use aliases. SQL won't allow you to use two tables with the same name without aliasing.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.