Updating current database with values from restored database and matching IDs

Question

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!

Does this answer your question? [SQL update query using joins](https://stackoverflow.com/questions/982919/sql-update-query-using-joins) — Jorge Campos, Mar 30 '20 at 16:19
Reason I marked it as duplicate is that you need to do an update with joins which is explained in above link — Jorge Campos, Mar 30 '20 at 16:20

score 1 · Accepted Answer · answered Mar 30 '20 at 16:20

1

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;

answered Mar 30 '20 at 16:20

Gordon Linoff

1,242,037
58
646
786

Thanks, but what is 'newdata'? Would that be my production database? – SkyeBoniwell Mar 30 '20 at 16:21
@SkyeBoniwell it's just an alias given to production table – Eldar Mar 30 '20 at 16:22
Would I need to add a 'AND newdata.monsterMapYear <> 7000;' to the end of the query to ensure I don't change rows I want to stay ? Thanks – SkyeBoniwell Mar 30 '20 at 18:06
@SkyeBoniwell . . . I think the existing `<>` does that. – Gordon Linoff Mar 30 '20 at 19:39

score 1 · Answer 2 · edited Mar 30 '20 at 17:47

Future reference: Put your updates in a "Begin Transaction" "Rollback Transaction" block for testing before you do the update.

  BEGIN TRANSACTION

  --Print out existing data

  SELECT * FROM monsterClass

  --Count the rows in the monsterClass table

  SELECT COUNT(*) FROM monsterClass

  --Update the data - Please check the number of rows updated versus the row count in the existing monster class printed earlier

  your update statement goes here

  --Print out the updated data

  SELECT * FROM monterClass

  --Undo the changes

  ROLLBACK TRANSACTION

At this point, you can verify that your updates work OK. When the data is updated correctly and you want to update the table, change the "ROLLBACK TRANSACTION" to "COMMIT TRANSACTION" and re-run the SQL query batch.

Updating current database with values from restored database and matching IDs

2 Answers2