[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Updating tables

Posted on 2011-03-10
6
Medium Priority
?
237 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What we learned in Webroot's webinar on multi-vector protection.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
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 sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

872 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