Help on UPDATE with EXIST

I have records in seven tables that must be inserted/updated in another table. The table structures are exactally alike. The seven tables contain Aircraft position data by time. The single table is the Radars table and should contain Aircraft data by time for each cooresponding entry in each of the seven tables.

Here is a storeProcedure that I wrote to insert data from one of the seven tables if an entry does not already exist in the Radars table:

Insert Into dbo.Radars
Select * From dbo.CG70
Where [Time] = @.eTime and
(not exists( select dbo.Radars.TRACKNUM from dbo.Radars where dbo.Radars.TRACKNUM = dbo.CG70.TRACKNUM));

The above code works fine in the insertion of a record into the Radars when one does not already exist. The following code should update the entire row in the Radars table when a TRACKNUM is found and the record should be replaces with the corresponding one from the CG70 table for a new Time value.

Update dbo.Radars
Set TRACKNUM = TRACKNUM
Select * From dbo.CG70
Where [Time] = @.eTime and
(exists( select dbo.Radars.TRACKNUM from dbo.Radars where dbo.Radars.TRACKNUM = dbo.CG70.TRACKNUM));

The above UPDATE does not work and I'm not currently smart enough to figure out why. Can someone point me in the proper direction? Please don't write my code for me, just tell me where I'm wrong.

thanks.

Quote:

Originally Posted by joecousins

I have records in seven tables that must be inserted/updated in another table. The table structures are exactally alike. The seven tables contain Aircraft position data by time. The single table is the Radars table and should contain Aircraft data by time for each cooresponding entry in each of the seven tables.

Here is a storeProcedure that I wrote to insert data from one of the seven tables if an entry does not already exist in the Radars table:

Insert Into dbo.Radars
Select * From dbo.CG70
Where [Time] = @.eTime and
(not exists( select dbo.Radars.TRACKNUM from dbo.Radars where dbo.Radars.TRACKNUM = dbo.CG70.TRACKNUM));

The above code works fine in the insertion of a record into the Radars when one does not already exist. The following code should update the entire row in the Radars table when a TRACKNUM is found and the record should be replaces with the corresponding one from the CG70 table for a new Time value.

Update dbo.Radars
Set TRACKNUM = TRACKNUM
Select * From dbo.CG70
Where [Time] = @.eTime and
(exists( select dbo.Radars.TRACKNUM from dbo.Radars where dbo.Radars.TRACKNUM = dbo.CG70.TRACKNUM));

The above UPDATE does not work and I'm not currently smart enough to figure out why. Can someone point me in the proper direction? Please don't write my code for me, just tell me where I'm wrong.

thanks.

Update query should look like

Update [TableName]
SET [ColumnName] = [Value]
WHERE [Condition]

In your query, You have a select statement in between SET and WHERE Clause, So after SET, you Select will return some values and your WHERE clause is not being used for Update.

Also Check you SET value. It should refer to the Column you want to Update with, not to itself.sql