Updating current database with values from restored database and matching IDs

I have a table for my game called monsterClass.

It’s a pretty simple table with these columns:

id
monsterName
monsterMapYear
monsterTreasure
locationId

And here’s sample data:

[  1 |  'Orc'         |  5000  | 'gold'   |  2932  ]
[  2 |  'Goblin'      |  6000  | 'silver' |  4611  ]
[  3 |  'Red Dragon'  |  3000  | 'gems'   |  8573  ]

etc

Well, foolish me was quickly trying to update a certain set of monsters, a mix of monsters with various names, with a new monsterMapYear (7000).

I did set the needed set of monsters to the have a monsterMapYear of 7000. But I inadvertently set every other monster, that wasn’t in the set I wanted to change, to have a monsterMapYear of NULL.

So I restored a previous backup to a new database, so I have that data that I accidently overwrote.

Now I’m trying to do an update to update the NULL values to what they were set to previously before I messed everything up.

So here’s my query so far:

UPDATE Game_Production.dbo.monsterClass
SET monsterMapYear = (
    SELECT monsterMapYear 
    FROM TMP_RESTORE_Game.dbo.monsterClass 
    WHERE monsterMapYear <> 7000
    AND monsterMapYear IS NOT NULL
    )

But I’m not sure how to match up the id of the monster in my production database to that of the one in my temporarily restored database.

Would there be a way to do that and safely run this query?

Thanks!

Answer

You should be able to use the id:

UPDATE newdata
    SET monsterMapYear = olddata.monsterMapYear 
FROM TMP_RESTORE_Game.dbo.monsterClass olddata JOIN
     Game_Production.dbo.monsterClass newdata
     ON olddata.id = newdata.id
WHERE newdata.monsterMapYear IS NULL OR
      newdata.monsterMapYear <> olddata.monsterMapYear;

Advertisement

Answer