?
Solved

updating

Posted on 2007-08-08
10
Medium Priority
?
163 Views
Last Modified: 2013-11-05
I need to update a field in a table called [newTable] with another field in a table called [oldTable].  Basically the [oldTable]  has the old and new ID fields in it.  The new table, [newTable] has the old ID and and empty field called [newtable.intListingID_NEW] that needs to be updated.

[oldTable]
intNewListingID (populated)
intOldListingID (populated)

[newTable]
intNewListingID (empty)
intOldListingID (populated)

Whats the best way of running a query to update [newTable.intNewListingID] so that [oldTable.intOldListingID] = [newTable.intOldListingID] ?

I hope this makes sence.

0
Comment
Question by:WebStalkers
  • 5
  • 5
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19656840
UPDATE newTable
  SET intNewListingID = o.intNewListingID
FROM newTable n
JOIN oldTable o
  ON o.intOldListingID  = i.intOldListingID
0
 
LVL 2

Author Comment

by:WebStalkers
ID: 19656913
I did that and its updating the field in all the listings of [newTable] with the first ID from [oldTable].
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19656932
?   can you show the sample data?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 2

Author Comment

by:WebStalkers
ID: 19657015
[newTable]
 intOldListingID  intNewListingID
12062      3623824      
12138      3623824      
18436      3623824      
19213      3623824      
60652      3623824      
60653      3623824      
60655      3623824      

[oldTable]
intNewListingID  intOldListingID
3614898      10682
3614899      12062
3614900      12138
3614901      18436
3614902      19213
3614903      20373
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19657046
then, my query should do the right job... have used that many times...
0
 
LVL 2

Author Comment

by:WebStalkers
ID: 19657085
hmm dont know what to say.

thanks anyway though
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19657102
well, I assume you posted the "before" data.
can you also the "after" data as you want to have it?
0
 
LVL 2

Author Comment

by:WebStalkers
ID: 19657119
I just figured it out.  You had the tables swapped in the FROM/JOIN statement

UPDATE newTable
  SET intNewListingID = o.intNewListingID
FROM oldTable o
JOIN newTable n
  ON o.intOldListingID  = n.intOldListingID

Thanks for your help
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19657137
FROM oldTable o
JOIN newTable n

and

FROM newTable n
JOIN oldTable o

will do in the same results...
0
 
LVL 2

Author Comment

by:WebStalkers
ID: 19657148
well it didnt here is what im telling you.  no big deal
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question