I am getting error messages when I try to delete from a table using
the values in the table itself. The intent is to delete all rows from
TableA where col_2 matches any of the col_1 values.
DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
y.col_2)
Error msg: The table 'TableA' is ambiguous.
Can this be done with SQL or should I use T-SQL with cursors here? 14 3607
Try EXISTS or IN
DELETE TableA
WHERE EXISTS (SELECT * FROM TableA y
WHERE TableA.col_2 = y.col_1)
Or did you want:
DELETE TableA
WHERE EXISTS (SELECT * FROM TableA y
WHERE TableA.col_1 = y.col_2)
As always, I recommend you test with SELECT queries first. (No warrantees
implied, etc...)
"php newbie" <ne**********@y ahoo.com> wrote in message
news:12******** *************** ***@posting.goo gle.com...
I am getting error messages when I try to delete from a table using
the values in the table itself. The intent is to delete all rows from
TableA where col_2 matches any of the col_1 values.
DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
y.col_2)
Error msg: The table 'TableA' is ambiguous.
Can this be done with SQL or should I use T-SQL with cursors here?
Try:
DELETE FROM x
FROM TableA x
INNER JOIN TableA y ON (x.col_1 = y.col_2)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"php newbie" <ne**********@y ahoo.com> wrote in message
news:12******** *************** ***@posting.goo gle.com... I am getting error messages when I try to delete from a table using the values in the table itself. The intent is to delete all rows from TableA where col_2 matches any of the col_1 values.
DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 = y.col_2)
Error msg: The table 'TableA' is ambiguous.
Can this be done with SQL or should I use T-SQL with cursors here?
Aaron,
Thanks for the tip. It worked!
On a related note, I am facing the same error when I try to update
TableA with data from the same TableA.
The command I used is this:
UPDATE TableA SET col_2 = y.col_2
FROM TableA x INNER JOIN TableA y
ON (x.col_1 = y.col_1)
Error message is: The table 'TableA' is ambiguous.
Do you have a similar solution?
"Aaron W. West" <ta******@hotma il.NO.SPAM> wrote in message news:<P4******* *************@s peakeasy.net>.. . Try EXISTS or IN
DELETE TableA WHERE EXISTS (SELECT * FROM TableA y WHERE TableA.col_2 = y.col_1)
As always, I recommend you test with SELECT queries first. (No warrantees implied, etc...)
On 14 Jul 2004 07:59:43 -0700, php newbie wrote: Aaron,
Thanks for the tip. It worked!
On a related note, I am facing the same error when I try to update TableA with data from the same TableA.
The command I used is this:
UPDATE TableA SET col_2 = y.col_2 FROM TableA x INNER JOIN TableA y ON (x.col_1 = y.col_1)
Error message is: The table 'TableA' is ambiguous.
Do you have a similar solution?
If you're using "x" as a label for TableA, you need to use it throughout.
UPDATE x SET x.col_2 = y.col_2
FROM TableA x
INNER JOIN TableA y
ON (x.col_1 = y.col_1)
I also got it to work like this:
CREATE TABLE #T (A int,B int)
INSERT #T SELECT 1,2
INSERT #T SELECT 2,2
INSERT #T SELECT 3,3
INSERT #T SELECT 4,3
INSERT #T SELECT 5,3
INSERT #T SELECT 6,4
INSERT #T SELECT 7,4
SELECT * FROM #T WHERE A IN (SELECT DISTINCT B FROM #T)
DELETE FROM #T WHERE A IN (SELECT DISTINCT B FROM #T)
>> On a related note, I am facing the same error when I try to update
TableA with data from the same TableA. <<
Why in the world do you think that SQL has a FROM clause in UPDATE and
DELETE??? You are writing unpredictable, proprietary code that EVEN
IF IT WAS ALLOWED, would not produce results.
There is no FROM clause in a Standard SQL UPDATE statement; it would
make no sense. Other products (SQL Server, Sybase and Ingres) also
use the UPDATE .. FROM syntax, but with different semantics. So it
does not port, or even worse, when you do move it, it trashes your
database. Other programmers cannot read it and maintaining it is
harder. And when Microsoft decides to change it, you will have to do
a re-write. Remember the deprecated "*=" versus "LEFT OUTER JOIN"
conversions? The last time the UPDATE FROM changed?
The correct syntax for a searched update statement is
<update statement> ::=
UPDATE <table name>
SET <set clause list>
[WHERE <search condition>]
<set clause list> ::=
<set clause> [{ , <set clause> }...]
<set clause> ::= <object column> = <update source>
<update source> ::= <value expression> | NULL | DEFAULT
<object column> ::= <column name>
The UPDATE clause simply gives the name of the base table or updatable
view to be changed.
Notice that no correlation name is allowed in the UPDATE clause; this
is to avoid some self-referencing problems that could occur. But it
also follows the data model in Standard SQL. When you give a table
expression a correlation name, it is to act as if a materialized table
with that correlation name has been created in the database. That
table then is dropped at the end of the statement. If you allowed
correlation names in the UPDATE clause, you would be updating the
materialized table, which would then disappear and leave the base
table untouched.
The SET clause is a list of columns to be changed or made; the WHERE
clause tells the statement which rows to use. For this discussion, we
will assume the user doing the update has applicable UPDATE privileges
for each <object column>.
* The WHERE Clause
As mentioned, the most important thing to remember about the WHERE
clause is that it is optional. If there is no WHERE clause, all rows
in the table are changed. This is a common error; if you make it,
immediately execute a ROLLBACK statement.
All rows that test TRUE for the <search condition> are marked as a
subset and not as individual rows. It is also possible that this
subset will be empty. This subset is used to construct a new set of
rows that will be inserted into the table when the subset is deleted
from the table. Note that the empty subset is a valid update that
will fire declarative referential actions and triggers.
* The SET Clause
Each assignment in the <set clause list> is executed in parallel and
each SET clause changes all the qualified rows at once. Or at least
that is the theoretical model. In practice, implementations will
first mark all of the qualified rows in the table in one pass, using
the WHERE clause. If there were no problems, then the SQL engine
makes a copy of each marked row in working storage. Each SET clause
is executed based on the old row image and the results are put in the
new row image. Finally, the old rows are deleted and the new rows are
inserted. If an error occurs during all of this, then system does a
ROLLBACK, the table is left unchanged and the errors are reported.
This parallelism is not like what you find in a traditional
third-generation programming language, so it may be hard to learn.
This feature lets you write a statement that will swap the values in
two columns, thus:
UPDATE MyTable
SET a = b, b = a;
This is not the same thing as
BEGIN ATOMIC
UPDATE MyTable
SET a = b;
UPDATE MyTable
SET b = a;
END;
In the first UPDATE, columns a and b will swap values in each row. In
the second pair of UPDATEs, column a will get all of the values of
column b in each row. In the second UPDATE of the pair, a, which now
has the same value as the original value of b, will be written back
into column b -- no change at all. There are some limits as to what
the value expression can be. The same column cannot appear more than
once in a <set clause list> -- which makes sense, given the parallel
nature of the statement. Since both go into effect at the same time,
you would not know which SET clause to use.
If a subquery expression is used in a <set clause>, and it returns a
single value, the result set is cast to a scalar; if it returns an
empty, the result set is cast to a NULL; if it returns multiple rows,
a cardinality violation is raised.
Same logic for the basic delete statement:
DELETE FROM Foobar
WHERE EXISTS
(SELECT *
FROM Foobar AS F1
WHERE Foobar.col_1 = F1.col_2)
Dan,
This works beautifully and also applies directly to the update query. Thanks a lot!
My gratitudes also go to Russ and Jim. I appreciate your help.
"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message news:<kZ******* **********@news read2.news.pas. earthlink.net>. .. Try:
DELETE FROM x FROM TableA x INNER JOIN TableA y ON (x.col_1 = y.col_2)
-- Hope this helps.
Dan Guzman SQL Server MVP ne**********@ya hoo.com (php newbie) wrote in message news:<12******* *************** ****@posting.go ogle.com>... I am getting error messages when I try to delete from a table using the values in the table itself. The intent is to delete all rows from TableA where col_2 matches any of the col_1 values.
DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 = y.col_2)
Error msg: The table 'TableA' is ambiguous.
Can this be done with SQL or should I use T-SQL with cursors here?
Hi,
try this:
DELETE x FROM TableA AS x INNER JOIN TableA AS y ON
(x.col_1 = y.col_2)
With best regards! ne**********@ya hoo.com (php newbie) wrote in message news:<12******* *************** ****@posting.go ogle.com>... I am getting error messages when I try to delete from a table using the values in the table itself. The intent is to delete all rows from TableA where col_2 matches any of the col_1 values.
DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 = y.col_2)
Error msg: The table 'TableA' is ambiguous.
Can this be done with SQL or should I use T-SQL with cursors here?
Hi,
try this:
DELETE x FROM TableA AS x INNER JOIN TableA AS y ON
(x.col_1 = y.col_2)
With best regards! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Bob Ganger |
last post by:
Hello,
I am working on a project using SQL Server 2000 with a database containing
about 10 related tables with a lot of columns containing text. The total
current size of the database is about 2 Gig. When I delete data from the
database, it takes a lot of system resources and monopolizes the database so
that all other query requests are slow as mud!
Ideally, I would like to be able to issue delete commands to the database on
a...
|
by: |
last post by:
Hi all,
I've been using C++ for quite a while now and I've come to the point where I need to overload new
and delete inorder to track memory and probably some profiling stuff too. I know that discussions of
new and delete is a pretty damn involved process but I'll try to stick to the main information I'm looking for
currently. I've searched around for about the last too weeks and have read up on new and overloading it to
some extent but...
|
by: Jonan |
last post by:
Hello,
For several reasons I want to replace the built-in memory management with
some custom built. The mem management itlsef is not subject to my question -
it's ok to the point that I have nice and working allocation deallocation
routines. However, I don't want to loose the nice extras of new operator,
like - constructor calling, typecasting the result, keeping the array size,
etc.
For another bunch of reasons, outside this scope I...
|
by: Robert Schneider |
last post by:
Hi to all,
I don't understand that: I try to delete a record via JDBC. But I always
get the error SQL7008 with the error code 3. It seems that this has
something to do with journaling, since the table from which I want to
delete has two foreign keys that references two other tables and it is
also referenced by another table. But this shouldn't be a problem, since I
set the commit mode to none (or *none) at all places where this makes...
|
by: robert |
last post by:
been ruminating on the question (mostly in a 390/v7 context) of
whether, and if so when, a row update becomes an insert/delete.
i assume that there is a threshold on the number of columns of
the table, or perhaps bytes, being updated where the engine just
decides, screw it, i'll just make a new one.
surfed this group and google, but couldn't find anything.
the context: we have some java folk who like to parametize/
| |
by: groleo |
last post by:
Hi list.
Simple question:
is it possible to override the global new/delete operators, without
using malloc/free?
I mean something in the ideea of the code below, which doesnt work
cause of
the recursivenes.
What I'm trying to say is how to override new/delete using the original
global
|
by: morz |
last post by:
i just search for a while in c++ groups but cannot find good answer.
i have code like this:
int **ptr;
ptr = new char *;
ptr = new int(5);
ptr = new int(16);
|
by: forbes |
last post by:
Hi,
I have a user that used the Query Wizard to create a query in Access.
Now she claims that her master table is missing all the data that was
excluded from the query. Can you create anything other than a select
query using the Wizard? What do you think happened to her data?
I am working remotely until Friday, so I can't get down to her office
and check out what she did.
|
by: rohits123 |
last post by:
To optimize the memory usage , I am using a huge block of memory for
my system and
then dividing the initial chunk in to 4 pools.
I have overloaded new and delete such that memory from a particular
pool can be taken and
freed .
As far as possible memory can be taken from particular pool and memory
is freed (comlete pool as whole ) when pool is completely used.
|
by: Anamika |
last post by:
Hello friends....
can anyone tell me what will happen when we do..."delete this"...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |