[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Updating tables

Posted on 2011-03-10
6
Medium Priority
?
238 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
Suggested Courses

607 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