overwrite the table with the new values only

Question

overwrite the table with the new values only

abhishek-484864

Valued Member

Points: 63
More actions
October 26, 2007 at 5:26 pm

#61394

hello guys,
I have this scenario and really having difficulty achieving it. There is a temp table #Table1 (EID int not null) that holds data like 1,2,3,4,5,6,7. Now i Inner join it with a table #Table2 (EID int not null,EName varchar(30) null) which has EID values 2,3,4 so obviously the resulting table would have EID as 2,3,4. Now i want the EID values of the above resulting table to overwrite the #Table1 values so that there are no 1,5,6,7 values. Even if 1,5,6,7 can be set to NULL can also help me.
I hope i made my scenario clear. Please help and also let me know if you have any questions.
Thanks a lot in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

Not too clear. Do you want to delete the records in Table1 or just set the EID to null. Either way to get the opposite result of your inner join do a left join and use "where table2.eid is null"

abhishek-484864 Valued Member Points: 63 More actions · Answer 2

The new Table1 should be either 2,3,4 OR 2,3,4,null,null,null,null because i need to use this updated Table1 again.

Basically the result of inner join of Table1 and Table2 should be the new content Table1.

Make sense?

Thank You.

goodguy SSCertifiable Points: 5456 More actions · Answer 3

If the related column in Table2 is an FK (i.e. it always has a matching record in Table1, then you can use:

DELETE TABLE1 WHERE Column1 NOT IN(SELECT Column1 from TABLE2)

Otherwise, you will need to use:

DELETE TABLE1 WHERE Column1 NOT IN(SELECT Column1 from TABLE2)

INSERT INTO TABLE1 (Column1) SELECT T2.Column1 INTO TABLE1(Column1) FROM TABLE2 T2 WHERE T2.Column1 NOT IN(SELECT T1.Column1 FROM TABLE1 T1)

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons