Solved

Updating tables

Posted on 2011-03-10
6
229 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Date Range Syntax Access 2003 10 45
MSSQL 2014 Query Synthax 8 38
combine an MS SQL string in Idera DM 9 31
Runtime 3044 error 14 17
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

758 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

19 Experts available now in Live!

Get 1:1 Help Now