deleting data using table prefix

I can run a select to retrieve data using a prefix 'a' for the specific table involved. However when I try to run a delete using the same criteria it fails telling me

Msg 102, Level 15, State 1,.......Line 1

Incorrect syntax near 'a'

The Select statement looks like:

select count(*) from schema.table a where a.customer_id=1234

The Delete looks like:

delete from schema.table a where a.customer_id=1234

What am I doing wrong here? and how can I prefix the table, because the command I want to run is much more complicated than the example above and it needs the prefix

According to BOL there is no alias for delete statement

Thanks

|||

You can use the TSQL extension for the DELETE statement like:

delete from schema.table

from schema.table a

where a.customer_id=1234

Deleting a range of tables

I have two SQL Server 2000 and i wanna transfer the tables between the servers, but just a specific range of tables can be overwrited.
Let me explain:
SERVER1 Tables (AAA010, AAA020, AAA040, AAA080, AAA090)
SERVER2 Tables (AAA010, AAA020, AAA040, AAA080, AAA090)
I need to transfer JUST the '%040' and '%080' tables from the SERVER2 to SERVER1.
It could be easy if was just 5 tables within each server, but are 1000+, AAA, AAB,...,XXX, XXZ.
Someone has a script to transfer only the marked tables ('%040') or to drop just the other tables ? How can i make this without selecting one by one the tables in the 'select the tables to transfer' option in sql enterprise manager ?

thankx !
ps. sorry for my english, i'm brazilian, i hope u understand...I don't know DTS well enough to provide an example but back in my Ingress days I had to move data between servers, not always the same servers and not always the same tables. My solution was to create a table that would hold information on the source and target server(s) and database(s) involved in the transfer.

My script would loop through the table selecting server pairs and then loop through the tables to be processed. As I recall it was a simple matter to process a list of tables or a description, '%020','%040'.

I don't know if this helps you or not. I will look through the documentation on DTS and see if I can come up with an example.

Deleteing specific rows from a table which have similar values

I have the following table and I'd like to delete rows which have the same
values in the fields MergeFromURN and MergeToURN
RecNo MergeFromURN MergeToURN
1 500 600
2 100 300
3 100 300
4 700 800
5 700 800
After my query I'd like my table to look like the following: -
RecNo MergeFromURN MergeToURN
1 500 600
2 100 300
4 700 800
I've no idea how to carry out this delete query as whenever I try I also
seem to delete both rows which are the same. Can someone help me to do this.
Thanks for any help anyone can give me.Hi Stephan,
this should work. Just replace 'myTable' with your table name.
Depending on the amount of data, you might want to create some indexes on
the temp-table.
Micha.
SELECT tab1.RecNo, tab1.MergeFromURN, tab1.MergeToURN
INTO #temp
FROM myTable tab1
JOIN myTable tab2 ON (tab1.RecNo <> tab2.RecNo AND tab1.MergeFromURN =
tab2.MergeFromUrn AND tab1.MergeToUrn = tab2.MergeToUrn)
DELETE
FROM myTable
WHERE RecNo IN (SELECT RecNo
FROM #temp)
AND RecNo NOT IN (SELECT MIN(RecNo)
FROM #temp
GROUP BY MergeFromURN, MergeToURN)
DROP TABLE #temp
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:76146231-179D-497C-ADA3-AE707F279984@.microsoft.com...
>I have the following table and I'd like to delete rows which have the same
> values in the fields MergeFromURN and MergeToURN
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 3 100 300
> 4 700 800
> 5 700 800
> After my query I'd like my table to look like the following: -
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 4 700 800
> I've no idea how to carry out this delete query as whenever I try I also
> seem to delete both rows which are the same. Can someone help me to do
> this.
> Thanks for any help anyone can give me.|||Hi Stephen,
May this statement solve your Problem
CREATE TABLE TABLENAME(RecNo INT, MergeFromURN INT ,MergeToURN
INT)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 1,500, 600)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 2,100, 300)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 3,100, 300 )
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 4,700, 800)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 5,700, 800)
SELECT * FROM TABLENAME
DELETE FROM TABLENAME WHERE RECNO NOT IN (SELECT MIN(RECNO) FROM
TABLENAME GROUP BY MergeFromURN,MergeToURN)
SELECT * FROM TABLENAME
DROP TABLE TABLENAME
If this statement does solve your purpose let me know.
Please post DDL,DML statements so that others can test their queries.
With warm regards
Jatinder Singh
Stephen wrote:
> I have the following table and I'd like to delete rows which have the same
> values in the fields MergeFromURN and MergeToURN
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 3 100 300
> 4 700 800
> 5 700 800
> After my query I'd like my table to look like the following: -
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 4 700 800
> I've no idea how to carry out this delete query as whenever I try I also
> seem to delete both rows which are the same. Can someone help me to do thi
s.
> Thanks for any help anyone can give me.

delete record

please help

i was trying to delete a specific record on a table but it still shows
the same record that should be deleted. i dont know how.bhetong22@.yahoo.com (bher2) wrote in message news:<f34a4b26.0401272320.6f8cd318@.posting.google.com>...
> please help
> i was trying to delete a specific record on a table but it still shows
> the same record that should be deleted. i dont know how.

How are you deleting it? How are you seeing it? Try something like
this from Query Analyzer:

delete from dbo.MyTable
where MyPrimaryKeyColumn = ...

select *
from dbo.MyTable
where MyPrimaryKeyColumn = ...

Simon|||bher2,
There may be several reasons for this. Here are some ideas:
- Do a select before you do the delete. See if that brings up the records
you think it will bring up.
- Try the select, then the delete in SQL Query analyzer. If you are doing
this in an application, you might not be seeing any errors that come up,
like foreign key errors.
- Try grabbing the PK first for the record(s) you want to delete, if there
is one and then delete by PK.
- Try updating the statistics on the table. If they are "broken", your db
might not be finding the record(s) you expect, if any.

Hope that helps,
Best regards,
Chuck Conover
www.TechnicalVideos.net

"bher2" <bhetong22@.yahoo.com> wrote in message
news:f34a4b26.0401272320.6f8cd318@.posting.google.c om...
> please help
> i was trying to delete a specific record on a table but it still shows
> the same record that should be deleted. i dont know how.|||Also, take a look at triggers on the table. There may be a trigger
defined as "instead of delete", which could prevent actual deletion of
the record and just mark it as deleted.

Chuck Conover wrote:
> bher2,
> There may be several reasons for this. Here are some ideas:
> - Do a select before you do the delete. See if that brings up the records
> you think it will bring up.
> - Try the select, then the delete in SQL Query analyzer. If you are doing
> this in an application, you might not be seeing any errors that come up,
> like foreign key errors.
> - Try grabbing the PK first for the record(s) you want to delete, if there
> is one and then delete by PK.
> - Try updating the statistics on the table. If they are "broken", your db
> might not be finding the record(s) you expect, if any.
> Hope that helps,
> Best regards,
> Chuck Conover
> www.TechnicalVideos.net
>
> "bher2" <bhetong22@.yahoo.com> wrote in message
> news:f34a4b26.0401272320.6f8cd318@.posting.google.c om...
>>please help
>>
>>i was trying to delete a specific record on a table but it still shows
>>the same record that should be deleted. i dont know how.
>

Delete on a single filegroup

I am running SQL 2005 SP2.
I have a partitioned table and was wondering if there is a view that could
tell me the data that resides on a specific filegroup. What I would like to
do is to delete only the data that resides on a specific filegroup. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200802/1Perhaps the $PARTITION() function will help you?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:801e77dbe7979@.uwe...
>I am running SQL 2005 SP2.
> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like to
> do is to delete only the data that resides on a specific filegroup. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200802/1
>|||> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like
> to
> do is to delete only the data that resides on a specific filegroup. Thx.
The query below will list partitions by filegroup. You can then delete the
data using $PARTITION function like Tibor mentioned or specify partitioning
column values that map to the partitions on the filegroup. If you have a
lot of data, you might consider switching partitions into a staging table
and then truncating the staging table.
SELECT
s.name AS SchemaName,
o.name AS TableName,
ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS
Boundary,
p.rows AS Rows
FROM sys.schemas AS s
JOIN sys.objects AS o ON
o.schema_id = s.schema_id
AND o.type = 'U'
JOIN sys.indexes AS i ON
i.object_id = o.object_id AND
i.index_id IN(0,1)
JOIN sys.partitions p ON
p.object_id = i.object_id AND
p.index_id = i.index_id
JOIN sys.data_spaces AS ds ON
ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps ON
ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf ON
pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2 ON
dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg ON
fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left ON
ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right ON
ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
ORDER BY
SchemaName,
TableName,
FileGroupName,
PartitionNumber;
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:801e77dbe7979@.uwe...
>I am running SQL 2005 SP2.
> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like
> to
> do is to delete only the data that resides on a specific filegroup. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200802/1
>

Delete on a single filegroup

I am running SQL 2005 SP2.
I have a partitioned table and was wondering if there is a view that could
tell me the data that resides on a specific filegroup. What I would like to
do is to delete only the data that resides on a specific filegroup. Thx.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like
> to
> do is to delete only the data that resides on a specific filegroup. Thx.
The query below will list partitions by filegroup. You can then delete the
data using $PARTITION function like Tibor mentioned or specify partitioning
column values that map to the partitions on the filegroup. If you have a
lot of data, you might consider switching partitions into a staging table
and then truncating the staging table.
SELECT
s.name AS SchemaName,
o.name AS TableName,
ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS
Boundary,
p.rows AS Rows
FROM sys.schemas AS s
JOIN sys.objects AS o ON
o.schema_id = s.schema_id
AND o.type = 'U'
JOIN sys.indexes AS i ON
i.object_id = o.object_id AND
i.index_id IN(0,1)
JOIN sys.partitions p ON
p.object_id = i.object_id AND
p.index_id = i.index_id
JOIN sys.data_spaces AS ds ON
ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps ON
ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf ON
pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2 ON
dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg ON
fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left ON
ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right ON
ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
ORDER BY
SchemaName,
TableName,
FileGroupName,
PartitionNumber;
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:801e77dbe7979@.uwe...
>I am running SQL 2005 SP2.
> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like
> to
> do is to delete only the data that resides on a specific filegroup. Thx.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
>