Update help with JOIN clause - 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 2008 Forums

Transact-SQL (2008)

Update help with JOIN clause

Author

Topic

learntsql

524 Posts

Posted - 2010-12-16 : 01:31:40

Hi All,
I have a two tables table1 and table2.
I have to update the table1's column based on the data available in table2.
for eg: table1 has ProductID column and table2 contains ProductID and StoreID columns where ProductID and StoreID are repeated i.e; single product may be sold in morethan one store.
And i have few more tbales which need to be joined with table1 to update table1 other columns data.
Now i need to check that for any product if any entry is there in second table(1 or more than 1) then i have to update in table1 data no need to check later entries.

Sorry If i am not clear.
PLZ....help me.
TIA.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-16 : 02:02:12

Please give us DDL, sample data and wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

learntsql

524 Posts

Posted - 2010-12-16 : 02:50:11

declare @productsSaleHistory table(ID int IDENTITY,ProductId varchar(10),ISSold bit,SaleDate date)
declare @Sales table(ID int IDENTITY,ProductID varchar(10),StoreID varchar(10),SaleDate date)

insert into @productsSaleHistory
select 'P1',null,GETDATE()-1
union
select 'P2',null,GETDATE()-1
union
select 'P3',null,GETDATE()-1
union
select 'P1',null,GETDATE()
union
select 'P2',null,GETDATE()
union
select 'P3',null,GETDATE()

insert into @Sales
select 'P1','S1',getdate()-1
union
select 'P1','S2',getdate()-1
union
select 'P1','S1',getdate()
union
select 'P1','S2',getdate()
union
select 'P2','S1',getdate()
union
select 'P2','S2',getdate()

select * from @Sales

SELECT PSH.ProductId,S.SaleDate
FROM @productsSaleHistory PSH
INNER JOIN @Sales S
ON PSH.ProductId = S.ProductID
WHERE PSH.SaleDate = CONVERT(DATE,GETDATE())

Assume GETDATE is '15-DEC-2010'
though P1 has more than one entry in @sales table check its existence.
inthis case P1 is sold in many stores on 15-Dec entries are repeated for 2 times.
desired output should be like

ProductID - ISSold - SaleDate
P1 - 1 - '15-Dec-2010'
P2 - 0 - '15-DEC-2010'
P3 - 0 - '15-DEC-2010'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-16 : 03:07:10

[code]declare @productsSaleHistory table(ID int IDENTITY,ProductId varchar(10),ISSold bit,SaleDate date)
declare @Sales table(ID int IDENTITY,ProductID varchar(10),StoreID varchar(10),SaleDate date)

insert into @productsSaleHistory
select 'P1',null,GETDATE()-1
union
select 'P2',null,GETDATE()-1
union
select 'P3',null,GETDATE()-1
union
select 'P1',null,GETDATE()
union
select 'P2',null,GETDATE()
union
select 'P3',null,GETDATE()

insert into @Sales
select 'P1','S1',getdate()-1
union
select 'P1','S2',getdate()-1
union
select 'P1','S1',getdate()
union
select 'P1','S2',getdate()
union
select 'P2','S1',getdate()
union
select 'P2','S2',getdate()

--select * from @Sales
--select * from @productsSaleHistory

--SELECT PSH.ProductId,S.SaleDate
--FROM @productsSaleHistory PSH
--INNER JOIN @Sales S
--ON PSH.ProductId = S.ProductID
--WHERE PSH.SaleDate = CONVERT(DATE,GETDATE())

update psh
set ISSold=1
from @productsSaleHistory psh
join (select ProductID,SaleDate from @Sales group by ProductID,SaleDate) s
on s.ProductID = psh.ProductID and s.SaleDate = psh.SaleDate
where psh.SaleDate='20101215'

select ProductId, coalesce(ISSOld,0) as ISSOld, SaleDate
from @productsSaleHistory
where SaleDate='20101215'[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources