Solved

Updating tables

Posted on 2011-03-10
6
230 Views
Last Modified: 2012-05-11
I have two tables example:

Table one:

Name ID      Name               TN      SC      NC  
100         John Smith
200         Tim Jones
300         Sam Nixon
400         Josh Walsh

Table Two:

Name ID      Name               TN      SC      NC  
100         John Smith            Y        N        R
300         Sam Nixon             N        Y        Y

I want to with a query overwrite the data from table two over the data in table one and without changing the the data I dont have any updates for in table one.
 
0
Comment
Question by:CiRMW
  • 4
  • 2
6 Comments
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35096925
You could try this

delete from table_one where NAME_ID IN (SELECT NAME_ID FROM table_two);

insert into table_one values(select * from table_two);
0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35096965
Or...
Update table_one
Set Name=(Select name from table_two where table_one.ID = table_two.ID), 
        TN=(Select TN from table_two where table_one.ID = table_two.ID), 
        SC=(Select SC from table_two where table_one.ID = table_two.ID), 
        NC=(Select NC from table_two where table_one.ID = table_two.ID)
WHERE table_one.ID IN (Select ID from table_two)

Open in new window

0
 

Author Comment

by:CiRMW
ID: 35096990
I need to keep the name id's in table one that dont have updates for.  If I delete them I will be deleting information not contained in table two.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35097034
The update statement(second post) is the best(thought of it after the other two, but the delete statement would only remove the rows from table 1 IF they are in table 2, and then would insert the data from table 2 into table 1.
0
 

Author Comment

by:CiRMW
ID: 35097215
So that statement you sent would only update the additional data from Table two to table one and not delete any data from table one
0
 
LVL 4

Accepted Solution

by:
Alex Matzinger earned 500 total points
ID: 35097295
That is correct, The only items that will be updated will be rows that are in both.  The rows that are just in table 1 will not be affected at all.  (this is true in all the SQL provided, but the UPDATE statement is the cleanest.

If you need a safety net to fall back on, submit a Select * from table_one before issuing any update or delete statements.  This way if you do issue a command that returns undesired results you will have a fall back to correct any issues.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This article describes some very basic things about SQL Server filegroups.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

930 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now