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 on Query

I have a table that has a field "Judge Name" that may contain names in the
format "J Jones" or "M Smith". I need to change that format to "Jones J" and
"Smith M". I am trying the following query thinking I can use that in an
Update query but I am getting the error:
"Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
expression."
I don't understand this? I am trying to say that if the second character is
a space, the name is in the old format and needs to be converted.
=============== Query ===================
IF (Select Substring([Judge Name], 2,1) From Test) = ' '
BEGIN
Select RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' + LEFT([Judge
Name],1)
From Test
END
Else
Select [Judge Name] From Test
========================================
=Wayne
If you want us to solve the problem, ,please post DDL+ sample data+ expected
result.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:e7zkFwGOGHA.140@.TK2MSFTNGP12.phx.gbl...
>I have a table that has a field "Judge Name" that may contain names in the
>format "J Jones" or "M Smith". I need to change that format to "Jones J"
>and "Smith M". I am trying the following query thinking I can use that in
>an Update query but I am getting the error:
> "Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression."
> I don't understand this? I am trying to say that if the second character
> is a space, the name is in the old format and needs to be converted.
> =============== Query ===================
> IF (Select Substring([Judge Name], 2,1) From Test) = ' '
> BEGIN
> Select RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' + LEFT([Judge
> Name],1)
> From Test
> END
> Else
> Select [Judge Name] From Test
> ========================================
=
>|||Here's one problem:

> IF (Select Substring([Judge Name], 2,1) From Test) = ' '
You must limit the number of rows returned to one for this to work, or
better: look up EXISTS in Books Online.
Anyway, guessing from your post you need the CASE expression. Look it up in
Books Online.
Try this (untested, since you haven't posted DLL and sample data):
select case
when Substring([Judge Name], 2,1)
then RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' +
LEFT([Judge
Name],1)
else [Judge Name]
end
from Test
ML
http://milambda.blogspot.com/|||You are trying to use IF in a way that simply is not how it works.
Try something along these lines:
SELECT CASE WHEN Substring([Judge Name], 2,1) = ' '
THEN RIGHT([Judge Name], LEN([Judge Name])-2)
+ ' '
+ LEFT([Judge Name],1)
ELSE [Judge Name]
END as NewJudgeName
FROM Test
Roy
On Thu, 23 Feb 2006 04:26:40 -0700, "Wayne Wengert"
<wayneSKIPSPAM@.wengert.org> wrote:

>I have a table that has a field "Judge Name" that may contain names in the
>format "J Jones" or "M Smith". I need to change that format to "Jones J" an
d
>"Smith M". I am trying the following query thinking I can use that in an
>Update query but I am getting the error:
>"Subquery returned more than 1 value. This is not permitted when the
>subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
>expression."
>I don't understand this? I am trying to say that if the second character is
>a space, the name is in the old format and needs to be converted.
>=============== Query ===================
>IF (Select Substring([Judge Name], 2,1) From Test) = ' '
> BEGIN
> Select RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' + LEFT([Judge
>Name],1)
> From Test
> END
>Else
> Select [Judge Name] From Test
> ========================================
=
>|||How about :
update test
set [Judge Name] =
RIGHT([Judge Name], LEN([Judge Name])-2) +
' ' + LEFT([Judge Name],1)
where Substring([Judge Name], 2,1) = ' '
No case statements or procedural logic should be needed if this is all you
are trying to do.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:e7zkFwGOGHA.140@.TK2MSFTNGP12.phx.gbl...
> I have a table that has a field "Judge Name" that may contain names in the
> format "J Jones" or "M Smith". I need to change that format to "Jones J"
and
> "Smith M". I am trying the following query thinking I can use that in an
> Update query but I am getting the error:
> "Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression."
> I don't understand this? I am trying to say that if the second character
is
> a space, the name is in the old format and needs to be converted.
> =============== Query ===================
> IF (Select Substring([Judge Name], 2,1) From Test) = ' '
> BEGIN
> Select RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' + LEFT([Judge
> Name],1)
> From Test
> END
> Else
> Select [Judge Name] From Test
> ========================================
=
>|||Thanks. I was using a wrong approach.
Wayne
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:etO11NIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> How about :
> update test
> set [Judge Name] =
> RIGHT([Judge Name], LEN([Judge Name])-2) +
> ' ' + LEFT([Judge Name],1)
> where Substring([Judge Name], 2,1) = ' '
> No case statements or procedural logic should be needed if this is all you
> are trying to do.
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:e7zkFwGOGHA.140@.TK2MSFTNGP12.phx.gbl...
> and
> is
>