troubleshooting Question

MS SQL Search one table then insert or update another table

Avatar of rwheeler23
rwheeler23Flag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
10 Comments1 Solution317 ViewsLast Modified:
I have two tables, one called TypePropertiesByItem and the other called ProductPropertyValue. I need to scan TypePropertiesByItem and then using the Item Number field look in the ProductTypeValue table and see if it already exists. If it does, update the PropertyValue field, if it does not exist, insert the entire record.

How do I combine these two sql statements to accomplish this? (the ProductType1 value of 577 is used to restrict the ProductType and is fixed)

select itemnmbr,article
from  TypePropertiesByItem  it
where ProdType1=577

if not exists(select top 1 from ProductPropertyValue where ProductID = it.ItemNmbr and PropertyID=577)
begin
      insert into ProductPropertyValue (ProductID,PropertyID,PropertyValue)
      values(it.ItemNmbr,577,it.Article)
end
else
begin
      update ProductPropertyValue
      set PropertyValue = it.Article
      where ProductID = it.ItemNmbr and PropertyID=577
end
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros