August 6, 2008 at 12:35 pm
UPDATE Products
SET Reference = m.NewRef
FROM Products PP, Mappings m
WHERE pp.Reference = m.OldRef
I am updating SOME records in products where they have a new reference in my mapping table. If I run this, and a product record is not in the clause, will it set the reference to blank? Or am I worrying too much and its fine?
Thanks
August 6, 2008 at 12:44 pm
any update statement will ALWAYS ruin the data.
if you're unsure what the results will be, copy the table into a holding table, run your update against the holding table, validate the results, modify the update if necessary, then once you're happy, run the update on the original table:
select *
into products_holding
from products
UPDATE Products_holding
SET Reference = m.NewRef
FROM Products_holding PP, Mappings m
WHERE pp.Reference = m.OldRef
August 6, 2008 at 1:26 pm
Will ALWAYS ruin the data???
August 6, 2008 at 1:29 pm
it replaces whatever was there with whatever's going to be there. basically, it destroys whatever was there to begin with. that pretty much ruins the data.
i was being more picky than anything, but it still holds. 🙂
did your data come out the way you expected?
August 6, 2008 at 3:05 pm
UPDATE PP
SET Reference = m.NewRef
FROM Products PP, Mappings m
WHERE pp.Reference = m.OldRef
_____________
Code for TallyGenerator
August 6, 2008 at 3:34 pm
Your query will only modify those records where:
WHERE pp.Reference = m.OldRef
the other records will be untouched.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply