Change value of row / field in table A depending on value in table B 2

[Guthro]

I have a table that has records removed and the underlying index order no longer matches the order of the numbers in the ID field. As soon as a new record is added, SQL studio shifts it somewhere else according to the original row orders.

I have reordered the master table and now want to assign the new ID to replace the old ID value in another table.

I have done all this on a spreadsheet but the table that needs updating has 30,000 or more rows and changing things manually is bound to bring in errors.

So I need to read table 2, ID field and if this ID matches the old ID in master table, replace it with the new ID.

The tables belong to an online game whereby you have approx. 1600 items (master table) and each player can have any number of these items (table 2).
If the ID change is inaccurate, player items will be transformed into something else.

Thanks for any guidance.

 

[bborissov]

Make a good backup.

UPDATE ChildTable SET NewId = MasterTable.NewId
FROM ChildTable
INNER JOIN MasterTable ON ChildTable.OldId = MasterTable.OldId

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

>Make a good backup.

That could be done inside the table itself, if you add a column for the old item ID, so:

ALTER TABLE playeritems ADD COLUMN olditemid
UPDATE playeritems SET olditemid = itemid && backup

Then do Borislav's update, for which you first create a table itemtransfer having olditemid, newitemid values.

UPDATE playeritems SET itemid = itemtransfer.newitemId
FROM playeritems
INNER JOIN itemtransfer ON playeritems.olditemid = itemtransfer.olditemid

Something like that would also allow you to roll back the whole change simply by UPDATE playeritems SET itemid = olditemid

Bye, Olaf.

 

[Guthro]

Thank you both for your reply.
I will try them on some backup tables and learn the SQL used.

 

[Guthro]

I've reached a point where I had to put this into practise.
I entered this into the SQL Pane of the old child (items) table.
Using verify, it gets the ok from MSSQL Studio 2012.

UPDATE RES_STDITEMS SET NEWID = IDTABLE.NEWID
FROM RES_STDITEMS
INNER JOIN IDTABLE ON RES_STDITEMS.ID = IDTABLE.oldID

After accepting the verification, it gives me this.

UPDATE RES_STDITEMS
SET NEWID = IDTABLE.NEWID
FROM RES_STDITEMS AS RES_STDITEMS_1 INNER JOIN
IDTABLE ON RES_STDITEMS_1.ID = IDTABLE.oldID CROSS JOIN
RES_STDITEMS
The result is all new IDs become 433. This is the new ID for old ID number 1.

I'm using old data from backups so there's no problem trying things out.
Thanks for any further guidance.

 

[Olaf Doschke]

Why do you change the working solution to non working code? Why do you do a cross join? What do you mean by "after accepting the verification"?

Bye, Olaf.

 

[Guthro]

I changed the tables and field names to match my actual database but the structure is the same.

On MSSQL Studio 2012, if you enter SQL in the pane, you get the option to verify the SQL. I always do this. It then puts the code how it likes it, with any brackets, tabs, separate lines etc.

Sorry if I messed up, I'm only a basic level user for SQL.

 

[Olaf Doschke]

Well, you must be talking about Visual Data Tools in Visual Studio. Seems the verifier is doing more than it should.

The cross join is making any record match with any other and so every newid is joined for any oldid, that's surely not equivalent to the initial query. In the end some random id wins and is put into the newid fields.

The first query you had will work correctly.

Bye, Olaf.

 

[Guthro]

I've been pulling my hair out on this for the last week or so now.
I used a third party SQL program and trying on MSSQL Studio without verifying, I was receiving errors saying that NEWID was invalid and that IDTABLE was out of bound.

Looking up the errors, I find the issue relates to scope. For some reason, the IDTABLE can't be seen.
Using table names for both tables fails, using aliases for both tables also fails. However, using an alias on the main table and actual table name of the IDTABLE succeeds.

For the record, the main data table is an established table within a group of about 10 tables, the IDTABLE was created simply to resolve the problem but resides in the same database group and in the same schema.
I understand the nature of scope but I don't understand why it was an issue here unless I would need to have backed up the database including the IDTABLE to somehow bring it in to the family of other tables.

Never-the-less, I have success now so thanks to Borislav and Olaf for taking the time to help. Your assistance was invaluable.