Update and Join

[Jashua Cumbie]

I have recently imported some data to SQL for a client, using files provided by another company. We made edits in Excel, using different vlookups and concatenates, and have now realized that one field called ShortFileName is not correct. The table (File) with the incorrect ShortFileName data has other relational tables that point to it (People2File, Notes2File, etc), so we need to leave the data there, with the unique identifiers staying the same, but update the data in the ShortFileName column. What I want to do is: correct the excel file, import it into a new table called 'File2', use another column called 'MatterId' to cross reference tables 'File' and 'File2', and update the 'File' table's ShortFileName column with the data from 'File2's ShortFileName.

Here is what I have come up with, but I havent tried it yet, and I am not sure the syntax is correct. This is probably the most advanced SQL query I have attempted.

UPDATE File
Set ShortFileName = File2.ShortFileName
From File
Inner Join File2
ON File.MatterID = File2.MatterID

It's important that the query is smart enough to match the rows on each table, find the match and return the ShortFileName from that row only, updating the real File table with the data from the new File2 table (which I am importing simply to be able to do the lookup using the Join statement).

If there is another way, even using SQL's import wizard, and avoiding creating the new table, please let me know.

Thanks in advance...

[Jashua Cumbie]

Or maybe simpler... would this work?

update dbo.File
set shortfilename = p.shortfilename
from dbo.File2 p
where p.matterid = matterid

[Oralloy]

Jashua,

Whatever you do, try it on a test copy of your critical table first. Especially if you're worried about accidentally taking the application down.

SELECT f.*
  INTO #TestFile
  FROM File f;
 
UPDATE #TestFile
  SET #TestFile.ShortFileName = File2.ShortFileName
  FROM #TestFile, File2
  WHERE (#TestFile.MatterID = File2.MatterID);
 
SELECT tf.*
  FROM #TestFile tf;

[Jashua Cumbie]

Thanks, I will give it a shot. Not sure I completely understand the syntax, but if it works, you're a genius regardless. ;-)

So if I remove the # sign from this code you wrote, I would end up with a table called testfile correct? And the code you supplied also does the update I am looking for? Or does SQL need the # sign to create the testfile table?

If I disable the software during the process, and the new table testfile ends up having the correct data, I could delete the old dbo.file table and rename testfile to file and fire the software back up correct?

[Jashua Cumbie]

Sorry for the original post, I edited it after I realized what you were doing/saying. Thanks again, I will give it a shot.

[Oralloy]

Jashua,

The octothorp (#) is used to indicate a temporary table.

That way you can generate the temporary temporary and verify that the UPDATE works without destroying your original table.

When you are satisfied that you are getting the correct result, then
1) back up your File table
2) issue the UPDATE statement, replacing "#TestFile" with "File".
3) check the result

And then, if and only if the result was correct, remove the backup.

Belt and suspenders - make sense?

Luck!

[Jashua Cumbie]

Awesome! You're the man! Thanks so much!

[Oralloy]

Glad to help.