Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

Updating tables

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
CiRMW
Asked:
CiRMW
  • 4
  • 2
1 Solution
 
Alex MatzingerDatabase AdministratorCommented:
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
 
Alex MatzingerDatabase AdministratorCommented:
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
 
CiRMWAuthor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Alex MatzingerDatabase AdministratorCommented:
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
 
CiRMWAuthor Commented:
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
 
Alex MatzingerDatabase AdministratorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now