By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,105 Members | 2,560 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,105 IT Pros & Developers. It's quick & easy.

Update and Join

P: 5
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...
Sep 8 '10 #1

✓ answered by 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.

Expand|Select|Wrap|Line Numbers
  1. SELECT f.*
  2.   INTO #TestFile
  3.   FROM File f;
  4.  
  5. UPDATE #TestFile
  6.   SET #TestFile.ShortFileName = File2.ShortFileName
  7.   FROM #TestFile, File2
  8.   WHERE (#TestFile.MatterID = File2.MatterID);
  9.  
  10. SELECT tf.*
  11.   FROM #TestFile tf;

Share this Question
Share on Google+
7 Replies


P: 5
Or maybe simpler... would this work?

update dbo.File
set shortfilename = p.shortfilename
from dbo.File2 p
where p.matterid = matterid
Sep 8 '10 #2

Oralloy
Expert 100+
P: 983
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT f.*
  2.   INTO #TestFile
  3.   FROM File f;
  4.  
  5. UPDATE #TestFile
  6.   SET #TestFile.ShortFileName = File2.ShortFileName
  7.   FROM #TestFile, File2
  8.   WHERE (#TestFile.MatterID = File2.MatterID);
  9.  
  10. SELECT tf.*
  11.   FROM #TestFile tf;
Sep 8 '10 #3

P: 5
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?
Sep 8 '10 #4

P: 5
Sorry for the original post, I edited it after I realized what you were doing/saying. Thanks again, I will give it a shot.
Sep 8 '10 #5

Oralloy
Expert 100+
P: 983
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!
Sep 8 '10 #6

P: 5
Awesome! You're the man! Thanks so much!
Sep 8 '10 #7

Oralloy
Expert 100+
P: 983
Glad to help.
Sep 8 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.