update/delete using two tables 1

[DerickD]

Hi 'yall

I have a simple problem, but I just can not find a way of getting the SQL server to see that it is simple

I have two tables, one with 10 records (TableOld) and one with 9 (TableNew).
9 of the records in both tables match.

I wish to update TableOld with the one record that does not appeare in table New. For Example :

UPDATE TableOld.Missing = 1
FROM TableOld LEFT OUTER JOIN
TableOld TableNew ON
TableOld.FileName = TableNew.FileName
WHERE (TableNew.FileName = NULL)

But this does not work....

What is the structure for the query ?

Thanks for reading this far!

DerickD

 

[swampBoogie]

insert into tableOld 
select * from tablenew 
where FileName not in (select filename from tableold)

 

[DerickD]

I do not think that I made my clear, I can see why you wrote that,

I want to update the field TableOld.Missing with '1' where the file in TableOld does not appear in TableNew.

This will allow me to then run a query in other tables to delete data where the filenames is the same and Missing = '1'

Then I can delete the record in TableOld where Missing = 1 and replace all data with TableNew.


To explain why I would like this :
I have a folder where I can place a file. Every 10 minuets the SQL checks the folder for new files and imports these new files into different tables. I also want the option to remove the file from the Folder, and then the SQL will then see that it is missing (TableOld.Import = 1 AND TableOld.FileName <> TableNew.FileName) then remove all the data that has been imported from this missing file.


Thanks,
DerickD

 

[swampBoogie]

update tableOld set missing = 1
where filename not in (select filename from tableNew)

 

[DerickD]

Perfect....!

Thanks alot.