CODEWITHSUNDEEP
sqlt-sqlsql-server-2008sql-server-2012
SkyeBoniwell
SkyeBoniwell

Reputation: 7112

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!

Upvotes: 0

Views: 48

Answers (2)

BobTsmith
BobTsmith

Reputation: 11

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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;

Upvotes: 1

Related Questions