• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 639
  • Last Modified:

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
0
I_s
Asked:
I_s
1 Solution
 
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
 
sdstuberCommented:
assuming you do want co2's nextcountdate to be set to co1's nextcountdate

then try this...
ee.txt
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now