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

Help needed to merge a few database - same tables

Hi all,
I am new to SQL and have to support application that uses MS SQL 2K.
I have to merge a few database , (with the same tables structures -
each
one include identification database Id) to one database file with the
same tables structures.
The merge should run on a daily bases ( all the new data on each table
will be from the last day until current day (one of the field in the
tables).
In addition if during the merge for some reason it "fail" a
rollback procedure is needed to the situation before the merge with
these tables.
I appreciate if anyone can help me with SQL script that will do the
work.
Or direct me how I should do it.
Thanks
RGI think you need transactional replication for this. It will replicate
transactions within a transactional context, with the commit or rollback
feature you require.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<ran_gl@.hotmail.com> wrote in message
news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
> Hi all,
> I am new to SQL and have to support application that uses MS SQL 2K.
> I have to merge a few database , (with the same tables structures -
> each
> one include identification database Id) to one database file with the
> same tables structures.
> The merge should run on a daily bases ( all the new data on each table
> will be from the last day until current day (one of the field in the
> tables).
> In addition if during the merge for some reason it "fail" a
> rollback procedure is needed to the situation before the merge with
> these tables.
> I appreciate if anyone can help me with SQL script that will do the
> work.
> Or direct me how I should do it.
> Thanks
> RG
>|||Hi, Hillary
How are your doing?
I was thinking about SNAPSHOT replication instead of transactional ,because
the OP said he needs it on daily bases.
Well, I think it is easy to handle , however I'm not sure about ROLLBACK in
case of the failure.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23NETQF2nGHA.4728@.TK2MSFTNGP03.phx.gbl...
>I think you need transactional replication for this. It will replicate
>transactions within a transactional context, with the commit or rollback
>feature you require.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <ran_gl@.hotmail.com> wrote in message
> news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
>|||Shalom Uri!
Snapshot will work as well. But it will mean you have to transfer all the
data and there is no transactional consistency. So in his case all the data
will move or none of it (if the snapshot fails). With transactional
replication, transactions are read from the tlog, decomposed into
constituent commands, and then these commands are applied within a
transactional context on the subscriber. Should a command fail, the entire
transaction is rolled back.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uIdwdc2nGHA.4124@.TK2MSFTNGP03.phx.gbl...
> Hi, Hillary
> How are your doing?
> I was thinking about SNAPSHOT replication instead of transactional
> ,because the OP said he needs it on daily bases.
> Well, I think it is easy to handle , however I'm not sure about ROLLBACK
> in case of the failure.
>
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23NETQF2nGHA.4728@.TK2MSFTNGP03.phx.gbl...
>

Help needed to merge a few database - same tables

Hi all,
I am new to SQL and have to support application that uses MS SQL 2K.
I have to merge a few database , (with the same tables structures -
each
one include identification database Id) to one database file with the
same tables structures.
The merge should run on a daily bases ( all the new data on each table
will be from the last day until current day (one of the field in the
tables).
In addition if during the merge for some reason it "fail" a
rollback procedure is needed to the situation before the merge with
these tables.
I appreciate if anyone can help me with SQL script that will do the
work.
Or direct me how I should do it.
Thanks
RGI think you need transactional replication for this. It will replicate
transactions within a transactional context, with the commit or rollback
feature you require.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<ran_gl@.hotmail.com> wrote in message
news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
> Hi all,
> I am new to SQL and have to support application that uses MS SQL 2K.
> I have to merge a few database , (with the same tables structures -
> each
> one include identification database Id) to one database file with the
> same tables structures.
> The merge should run on a daily bases ( all the new data on each table
> will be from the last day until current day (one of the field in the
> tables).
> In addition if during the merge for some reason it "fail" a
> rollback procedure is needed to the situation before the merge with
> these tables.
> I appreciate if anyone can help me with SQL script that will do the
> work.
> Or direct me how I should do it.
> Thanks
> RG
>|||Hi, Hillary
How are your doing?
I was thinking about SNAPSHOT replication instead of transactional ,because
the OP said he needs it on daily bases.
Well, I think it is easy to handle , however I'm not sure about ROLLBACK in
case of the failure.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23NETQF2nGHA.4728@.TK2MSFTNGP03.phx.gbl...
>I think you need transactional replication for this. It will replicate
>transactions within a transactional context, with the commit or rollback
>feature you require.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <ran_gl@.hotmail.com> wrote in message
> news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
>> Hi all,
>> I am new to SQL and have to support application that uses MS SQL 2K.
>> I have to merge a few database , (with the same tables structures -
>> each
>> one include identification database Id) to one database file with the
>> same tables structures.
>> The merge should run on a daily bases ( all the new data on each table
>> will be from the last day until current day (one of the field in the
>> tables).
>> In addition if during the merge for some reason it "fail" a
>> rollback procedure is needed to the situation before the merge with
>> these tables.
>> I appreciate if anyone can help me with SQL script that will do the
>> work.
>> Or direct me how I should do it.
>> Thanks
>> RG
>|||Shalom Uri!
Snapshot will work as well. But it will mean you have to transfer all the
data and there is no transactional consistency. So in his case all the data
will move or none of it (if the snapshot fails). With transactional
replication, transactions are read from the tlog, decomposed into
constituent commands, and then these commands are applied within a
transactional context on the subscriber. Should a command fail, the entire
transaction is rolled back.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uIdwdc2nGHA.4124@.TK2MSFTNGP03.phx.gbl...
> Hi, Hillary
> How are your doing?
> I was thinking about SNAPSHOT replication instead of transactional
> ,because the OP said he needs it on daily bases.
> Well, I think it is easy to handle , however I'm not sure about ROLLBACK
> in case of the failure.
>
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23NETQF2nGHA.4728@.TK2MSFTNGP03.phx.gbl...
>>I think you need transactional replication for this. It will replicate
>>transactions within a transactional context, with the commit or rollback
>>feature you require.
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> <ran_gl@.hotmail.com> wrote in message
>> news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
>> Hi all,
>> I am new to SQL and have to support application that uses MS SQL 2K.
>> I have to merge a few database , (with the same tables structures -
>> each
>> one include identification database Id) to one database file with the
>> same tables structures.
>> The merge should run on a daily bases ( all the new data on each table
>> will be from the last day until current day (one of the field in the
>> tables).
>> In addition if during the merge for some reason it "fail" a
>> rollback procedure is needed to the situation before the merge with
>> these tables.
>> I appreciate if anyone can help me with SQL script that will do the
>> work.
>> Or direct me how I should do it.
>> Thanks
>> RG
>>
>