473,394 Members | 1,721 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Update and Join

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;

7 1627
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
988 Expert 512MB
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
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
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
988 Expert 512MB
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
Awesome! You're the man! Thanks so much!
Sep 8 '10 #7
Oralloy
988 Expert 512MB
Glad to help.
Sep 8 '10 #8

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

Similar topics

6
by: Dave | last post by:
HI All I have a process that I am trying to accomplish with one statement. I cannot think of any way to do it other than using a cursor. I was wondering if anyone could point me in the right...
17
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
2
by: serge | last post by:
/* This is a long post. You can paste the whole message in the SQL Query Analyzer. I have a scenario where there are records with values pointing to wrong records and I need to fix them using an...
2
by: Mike Leahy | last post by:
Hello all, This question is related to updating tables - is there any way to calculate or update the values in a column in a table to the values in a field produced by a query result? An...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
2
by: Sim Zacks | last post by:
The following query updated all the rows in the AssembliesBatch table, not just where batchID=5. There are 2 rows in the AssembliesBatch table with batch ID of 5 and I wanted to update both of...
4
by: Darren Woodbrey | last post by:
I am trying to update 1 table with the top records from another table for each record in the first table UPDATE HPFSLOWMOVING SET TOP 1 LASTRCTDATE = (SELECT DOCDATE FROM IV30300 INNER JOIN...
5
by: Chris | last post by:
I got got a pile of bad email addresses to update in our SQL database. I know how to do this for individual records using the update command. Is there a way to execute an update using the list of...
0
by: svgeorge | last post by:
I want to update several tables using one stored procedure. How can i do this I mean the syntax.etc. declaration etc. I know the basic syntax as below CREATE PROCEDURE <Procedure_Name, sysname,...
3
by: Michel Esber | last post by:
Hi all, DB2 V8 LUW FP 15 There is a table T (ID varchar (24), ABC timestamp). ID is PK. Our application needs to frequently update T with a new value for ABC. update T set ABC=? where ID...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.