Help with a update Query??????

[zeeshan13]

Hi All,

I have a table called Test.

It has the following fields all with nvarchar data type.
ZoneID, ProdID, ProdDesc

Now, apart from different zoneids, there is a id called GOLBAL

Irrespective of zoneid, I want to update the descripiton to same as the GLOBAL level description.

Here's what I need to do.


1. Get a list (lets say ListA) of distinct ZoneID, ProdID, ProdDesc where ZoneID is GLOBAL
2. Get a list (lets say ListB) of distinct ZoneID, ProdID, ProdDesc where ZoneID is not global
3. Compare ListA & ListB, set all the ProdDesc of ListB to same as the ProdDesc of ProdListB, where ProdID in both ListA & ListB is same, but ProdDesc in both ListA & ListB is different.

I can write the queries for step 1 & 2.

1.
select distinct distinct ZoneID, ProdID, ProdDesc into ListA from Test where ZonID='GLOBAL'

2.select distinct distinct ZoneID, ProdID, ProdDesc into ListB from Test where ZonID<>'GLOBAL'

3.
???


Can some help me here to write a update query, using the critiera in step3 (defined above).


Looking for a quick response.

Thanks

 

[RileyCat]

Piece 'o cake, Rocky. But, need some clarification first, please . . .

You said . . .

Compare ListA & ListB, set all the ProdDesc of ListB to same as the ProdDesc of ProdListB, where ProdID in both ListA & ListB is same, but ProdDesc in both ListA & ListB is different.

Do you mean to say . . .

Compare ListA & ListB, set all the ProdDesc of ListB to same as the ProdDesc of ListA, where ProdID in both ListA & ListB is same, but ProdDesc in both ListA & ListB is different.

Please confirm or verify.

Thanks in advance!

Stay Cool Ya'll!

-- Kristin

 

[zeeshan13]

RileyCat,
Yeah you are right...

Compare ListA & ListB, set all the ProdDesc of ListB to same as the ProdDesc of ListA, where ProdID in both ListA & ListB is same, but ProdDesc in both ListA & ListB is different.

Thats what I meant...

Can you please help???

Thanks,

 

[RileyCat]

Since I don't believe in writing the answer out word for word, the following example should provide enough information for you to be able to script this to your needs.

create table #tmpTable_A
(
	ZoneID	int,
	ProdID  int,
	ProdDesc int
)

create table #tmpTable_B
(
	ZoneID	int,
	ProdID  int,
	ProdDesc int
)

insert into #tmpTable_A values  (1, 1, 1)
insert into #tmpTable_A values  (1, 2, 2)
insert into #tmpTable_A values  (1, 2, 3)
insert into #tmpTable_A values  (1, 2, 4)
insert into #tmpTable_A values  (1, 5, 6)
insert into #tmpTable_A values  (1, 5, 8)

insert into #tmpTable_B values  (1, 1, 1)
insert into #tmpTable_B values  (1, 2, 3)
insert into #tmpTable_B values  (1, 2, 4)
insert into #tmpTable_B values  (1, 2, 5)
insert into #tmpTable_B values  (1, 5, 7)
insert into #tmpTable_B values  (1, 5, 9)

select	*
from	#tmpTable_A (nolock)

select	*
from	#tmpTable_B (nolock)

update	#tmpTable_B
set	#tmpTable_B.ProdDesc = #tmpTable_A.ProdDesc
from	#tmpTable_B
	inner join #tmpTable_A on
		#tmpTable_B.ZoneID = #tmpTable_A.ZoneID
where	#tmpTable_B.ProdDesc != #tmpTable_A.ProdDesc

select	*
from	#tmpTable_A

select	*
from	#tmpTable_B

drop table #tmpTable_A
drop table #tmpTable_B

Stay Cool Ya'll!

-- Kristin

 

[RileyCat]

This might make it a bit more clear . . .

create table #tmpTable_A
(
	ZoneID	int,
	ProdID  int,
	ProdDesc int
)

create table #tmpTable_B
(
	ZoneID	int,
	ProdID  int,
	ProdDesc int
)

insert into #tmpTable_A values  (1, 1, 1)
insert into #tmpTable_A values  (1, 2, 2)
insert into #tmpTable_A values  (1, 2, 3)
insert into #tmpTable_A values  (1, 2, 4)
insert into #tmpTable_A values  (1, 5, 6)
insert into #tmpTable_A values  (1, 5, 8)

insert into #tmpTable_B values  (1, 1, 1)
insert into #tmpTable_B values  (1, 2, 12)
insert into #tmpTable_B values  (2, 2, 14)
insert into #tmpTable_B values  (3, 2, 15)
insert into #tmpTable_B values  (2, 5, 18)
insert into #tmpTable_B values  (3, 5, 23)

select	*
from	#tmpTable_A (nolock)

select	*
from	#tmpTable_B (nolock)

update	#tmpTable_B
set	#tmpTable_B.ProdDesc = #tmpTable_A.ProdDesc
from	#tmpTable_B
	inner join #tmpTable_A on
		#tmpTable_B.ZoneID = #tmpTable_A.ZoneID
where	#tmpTable_B.ProdDesc != #tmpTable_A.ProdDesc

select	*
from	#tmpTable_A

select	*
from	#tmpTable_B

drop table #tmpTable_A
drop table #tmpTable_B

Stay Cool Ya'll!

-- Kristin

 

[zeeshan13]

RileyCat,

This is what I had before:

1.
select distinct distinct ZoneID, ProdID, ProdDesc into ListA from Test where ZonID='GLOBAL'


Now, before updating, using a query like yours, if I do the following select:



select * from Test t
join ListA g on (t.ProdID=g.ProdID)
where t.ProdDesc!=g.ProdDesc
order by t.ProdID

I am getting results, in which ProdID & ProdDesc both are same. It looks the line (where a.ProdDesc!=g.ProdDesc) is not working effectively.

I only want to update records, where ProdID is same, but ProdDesc is different.

Am I doing something wrong, or is there anyother way (may be with join) to do it.


Thanks,