CiRMW
asked on
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.
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.
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)ASKER
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.
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.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
delete from table_one where NAME_ID IN (SELECT NAME_ID FROM table_two);
insert into table_one values(select * from table_two);