UPDATE statement won't update anything

Question

I've been creating a recipe database for a class project using SSMS. I have one table with all the recipe names that I'd inserted into a lookup table (RecipeDetails), but I'd left out their associated IDs, so I wanted to write a bit of code to update my table with those values. I feel like this code should work fine (it seemed to work in the past, but I screwed up something unrelated and had to restore my most recent database backup), but now it just isn't. It says it's affected all the rows I expect it to affect, but those rows still list the RecipeID as NULL.

I'm pulling my data from RecipesTable, which includes the names of each recipe and an ID for each. In RecipeDetails I have a column for RecipeName, RecipeID, Ingredient, IngredientID, and an ID for each row in the table. As of now, I have all my recipes in the table, but not their associated ID. I would like to move the ID's over from one table to another.

UPDATE rd    
SET rd.RecipeID = rt.RecipeID    
FROM RecipeDetail AS rd    
FULL JOIN RecipesTable AS rt ON rd.RecipeID = rt.RecipeID    
WHERE rt.RecipeName = rd.RecipeName;

Not work means the ID's are still NULL in my RecipeDetail table. RecipeName comes from the RecipesTable, the table that lists all the recipes by name but does not include any ingredients. — blork, Jun 23 '20 at 01:31
Your FULL JOIN is logically converted to an inner join due to the where clause. You join on RecipeID so it does not seem to make any logical sense to set the same columns - because you can't "see" any change when you set something to the existing value. Presumably you added the typo "rt.RecipID". — SMor, Jun 23 '20 at 01:53
You should also fix your schema. There should be a foreign key from RecipesDetail to RecipesTable. If you intend to join on RecipeName, that should be a unique column in RecipesTable. And RecipesDetail should not duplicate the name - that is an attribute of the recipe and not of the detail. Seems there is much work to be done here. — SMor, Jun 23 '20 at 01:59
@SMor My foreign keys are already set up as you recommend. I don't intend to join on RecipeName, but on RecipeID. There is, as you suggest, work to be done here. — blork, Jun 23 '20 at 02:13
@GordonLinoff I've edited my question to clarify, please let me know if I can be more clear. — blork, Jun 23 '20 at 02:21
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Jun 23 '20 at 03:13
Duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/q/4707673/3404097) This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 23 '20 at 03:15

Gordon Linoff · Accepted Answer · 2020-06-23T01:47:16.300

1

You should use an inner join rather than an outer join.

UPDATE rd
    SET rd.RecipeID = rt.RecipeID
    FROM RecipeDetail rd JOIN
         RecipesTable rt
         ON rd.RecipeName = rt.RecipeName;

edited Jun 23 '20 at 01:47

answered Jun 23 '20 at 01:29

Gordon Linoff

1,242,037
58
646
786

The inner join does nothing, it only updates the rows in which I've already hard-coded the RecipeID into (which don't need updating, as you might imagine) – blork Jun 23 '20 at 01:33
@blork . . . That is a really good observation. I updated the answer. – Gordon Linoff Jun 23 '20 at 01:47
No dice. Like I said, there are no `RecipeID` values included in `RecipeDetail`, so an `inner join` won't update those values that need to be updated. A `full join` worked fine last night (I think--I'm starting to wonder if it was all just a beautiful dream). SSMS is telling me that those rows (when using the `full join`) have been updated, but when I double check they still appear to be `null`. – blork Jun 23 '20 at 01:58
@blork . . . The join is on the name, not the id. – Gordon Linoff Jun 23 '20 at 02:28
And this is why reading comprehension is important. Thank you so much! – blork Jun 23 '20 at 02:31

UPDATE statement won't update anything

1 Answers1