SQL Replace

[jshurst]

I have two tables. tblProduct and tblProductCrossReference. Let's say that tblProduct only has one column (ProductID), and the second table has two columns (OldProductID and NewProductID).

I want to compare all values in the first table to the second one and if I find that tblProduct.ProductID and tblProductCrossReference.OldProductID are equal I would like to replace the value of tblProduct.ProductID with tblProductCrossReference.NewProductID.

Can someone help me out with this? Thanks in advance.

 

[SQLBill]

Maybe,

UPDATE tblProduct
 SET tblProduct.ProductID = tblProductCrossReference.NewProductID
 FROM tblProduct
  LEFT OUTER JOIN tblProductCrossReference
    ON tblProduct.ProductID = tblProductCrossReference.OldProductID

As always, test it first.

-SQLBill

Posting advice: FAQ481-4875

 

[jshurst]

Almost. The only problem is that I want it to skip the ones that are not found. This query sets the values to <NULL> if a value is not found in the ProductCrossReference table. I want it to just leave these values alone.

Thanks for the help so far.

 

[SQLDenis]

take out the LEFT OUTER from the join then, make it a regular join

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jshurst]

It seems to work if I use RIGHT OUTER JOIN. Is this correct?

 

[jshurst]

I used just JOIN too, but I can't tell a difference in the results.

 

[SQLDenis]

then you must have NULL values in the table(s)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

Maybe:

UPDATE tblProduct
 SET tblProduct.ProductID = tblProductCrossReference.NewProductID
 FROM tblProduct
  LEFT OUTER JOIN tblProductCrossReference
    ON tblProduct.ProductID = tblProductCrossReference.OldProductID
WHERE tblProductCrossReference.NewProductID IS NOT NULL

-SQLBill

Posting advice: FAQ481-4875

 

[alidabiri]

I have two tables. tblProduct and tblProductCrossReference. Let's say that tblProduct only has one column (ProductID), and the second table has two columns (OldProductID and NewProductID).

I want to compare all values in the first table to the second one and if I find that tblProduct.ProductID and tblProductCrossReference.OldProductID are equal I would like to replace the value of tblProduct.ProductID with tblProductCrossReference.NewProductID.

do this:

update tblProduct, tblProductCrossReference
set tblProduct.productID = tblProductCrossReference.NewProductID
where tblProduct.ProductID =
tblCrossReference.OldProductID

it may seem that you're updating two tables, but, in actuality, you're setting only one column.
i have done this many times, where i update missing county names from a table that contains county names and county numbers.