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

General SQL Server Forums

New to SQL Server Programming

update

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-04-03 : 12:44:39

Hi.Im sorry I think this must be so simple Im new.
I have a table and I need to update productDescription null fields for those productCodes that are same & have productDescription value.
I mean if I have table like this:

productID productCode productDescription description_match
13575 40000 Nutrition Shake, Chocolate Cream 1
35650 400000 NULL
13583 40004 Nutrition Shake, Vanilla Cream1
35667 400040 NULL

I need to update it like this:
productID productCode productDescription description_match
13575 40000 Nutrition Shake, Chocolate Cream 1
35650 40000 Nutrition Shake, Chocolate Cream 0
13583 40004 Nutrition Shake, Vanilla Cream 1
35667 40004 Nutrition Shake, Vanilla Cream 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-03 : 12:53:42

what value do you want to update when there are more than one row with not null productDescription values for same product code? also is last 0 intentional for all duplicate product codes?

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-04-03 : 13:20:12

sorry I think I showed the values incorrect:
for instance when we have one uniq productCode value for 2 items:

productID----productCode-----productDescription-----description_match
13575--------40000-----------Nutrition Shake, Chocolate Cream-----1
35650--------40000-----------NULL---------------------------------0
13583--------40004-----------Nutrition Shake, Vanilla Cream-------1
35667--------40004-----------NULL---------------------------------0

I'd like to update productDescription Null to productDescription value for the same productCode :

productID----productCode-----productDescription-----description_match
13575--------40000-----------Nutrition Shake, Chocolate Cream-----1
35650--------40000-----------Nutrition Shake, Chocolate Cream----0
13583--------40004-----------Nutrition Shake, Vanilla Cream-------1
35667--------40004-----------Nutrition Shake, Vanilla Cream-------0

thanks in advanced

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-03 : 13:26:25

[code]
UPDATE t
SET t.productDescription=t1.productDescription
FROM table t
JOIN table t1
ON t.productCode =t1.productCode
WHERE t1.description_match=1
AND t.description_match=0
[/code]

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-04-03 : 14:53:57

I know that I can get the productDescription fields wich are null like this:

select max(productDescription), productcode from dup_products group by productcode

but I dont know how should i update the productDescription Null fields
with that.
this query doesnt work:

update dup_products set product_Description = (select max(product_Description), product_europa_code from dup_products group by product_europa_code)

Neither this one:

update dup_products set product_Description = product_Description
where product_Description in (select max(product_Description), product_europa_code from dup_products group by product_europa_code)

I dont know what to do :(

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-03 : 15:09:36

try this,

create table #s (productID int,productCode int,productDescription varchar(1000),description_match int)

Go

insert into #s
select 13575,40000,'Nutrition Shake,Chocolate Cream',1 union all
select 35650,40000,NULL,0 union all
select 13583,40004,'Nutrition Shake,Vanilla Cream',1 union all
select 35667,40004,NULL,0 

Go

update a
set productDescription=(select  top 1 productDescription from #s where productCode=a.productCode and productDescription is not null)
from #s a

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-04-03 : 15:34:07

Didnt work :(

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-04-03 : 16:01:34

finially this was the answer:

UPDATE dup_products
SET dup_products.product_Description=dup_productsCPY3.product_Description
FROM dup_productsCPY3 INNER JOIN dup_products ON dup_productsCPY3.product_id=dup_products.product_id
where
dup_products.product_match_desc=0

thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources