DENY permission,SQL server: syntax

Showing posts with label syntax. Show all posts

Thursday, March 29, 2012

Deleting SP syntax check please...

I'm trying to write a SP that will delete records from a few tables and then
count to see if any records are for some unknown reason, left over. The
goal is to have the SP return "0" upon successful deletions (used and called
from asp.net code). It it return a value greater than 0 than I know
something went wrong.
Pasted below is my attempt. I keep getting a syntax error near the word
"DELETE" in the first delete command.
What am I doing wrong? Before someone suggests I create relationships
between all these tables, the answer is I can't. I'm working with another
"old-school" developer who doesn't like them and likes to do all his
relationships "programmatically" thru code. My hands are tied so I need to
delete from each table separately.
THANKS!
CREATE PROCEDURE sp_DeletelApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE ID = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count(ID) As Applications FROM Applications WHERE ID = @.intApplicationID) +
(SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GOGroove
> DELETE FROM Applications WHERE ID = @.intApplicationID
Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
> I'm trying to write a SP that will delete records from a few tables and
> then count to see if any records are for some unknown reason, left over.
> The goal is to have the SP return "0" upon successful deletions (used and
> called from asp.net code). It it return a value greater than 0 than I
> know something went wrong.
> Pasted below is my attempt. I keep getting a syntax error near the word
> "DELETE" in the first delete command.
> What am I doing wrong? Before someone suggests I create relationships
> between all these tables, the answer is I can't. I'm working with another
> "old-school" developer who doesn't like them and likes to do all his
> relationships "programmatically" thru code. My hands are tied so I need
> to delete from each table separately.
> THANKS!
>
> CREATE PROCEDURE sp_DeletelApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE ID = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count(ID) As Applications FROM Applications WHERE ID => @.intApplicationID) +
> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>|||Thanks but no luck. I enclosed all my "ID's" in brackets and still the same
error when checking the syntax:
CREATE PROCEDURE spDeleteCapitalApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =@.intApplicationID) +
(SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left over.
>> The goal is to have the SP return "0" upon successful deletions (used and
>> called from asp.net code). It it return a value greater than 0 than I
>> know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the word
>> "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do all
>> his relationships "programmatically" thru code. My hands are tied so I
>> need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>|||:-))),Now I see , you have missed AS in the stored procedure
CREATE PROCEDURE spDeleteCapitalApplication
@.intApplicationID Integer
AS
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
> same error when checking the syntax:
>
> CREATE PROCEDURE spDeleteCapitalApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] => @.intApplicationID) +
> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
>> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left over.
>> The goal is to have the SP return "0" upon successful deletions (used
>> and called from asp.net code). It it return a value greater than 0 than
>> I know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the word
>> "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do all
>> his relationships "programmatically" thru code. My hands are tied so I
>> need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>|||D'oh!
(slaps forehead)
Thanks!!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u8PDSaXXGHA.4620@.TK2MSFTNGP04.phx.gbl...
> :-))),Now I see , you have missed AS in the stored procedure
> CREATE PROCEDURE spDeleteCapitalApplication
> @.intApplicationID Integer
> AS
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>
>
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
>> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
>> same error when checking the syntax:
>>
>> CREATE PROCEDURE spDeleteCapitalApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =>> @.intApplicationID) +
>> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
>> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left
>> over. The goal is to have the SP return "0" upon successful deletions
>> (used and called from asp.net code). It it return a value greater than
>> 0 than I know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the
>> word "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do
>> all his relationships "programmatically" thru code. My hands are tied
>> so I need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>>
>

Deleting SP syntax check please...

deleting rows based on nvarchar data

I have a table that contains rows that I would like to delete based on a field and it's contents.
What is the correct syntax to script the removal of these rows based field parameter?delete tableA where fieldB = ?

Is that what you mean?|||Kinda. I only have one table and want to delete specific rows from that table that have a specific data within a certain field.

Let be more specific. I have a table (tableA) with 10 fields. Field 3 has data that does not conform to a datetime format and I would like to remove it. The field is currently a nvarchar(50) type (2003-10-10).

I want to remove rows that contain data that is looks like this
(0020-10-10). Make sense?|||delete from table where field3 ='0020-10-10'|||Perhaps you can use the ISDATE() function, which returns 1 if a string can be converted to a valid date, and 0 if it cannot.

Try this query:

select *
from YourTable
where ISDATE([Column3]) = 0

If this returns the rows you want deleted, then change the query to a delete query:

delete
from YourTable
where ISDATE([Column3]) = 0|||right but I forgot to mention, there are all kinds of variation of that date.

I ran a script that reads as follows to help identify data within a field that does not fit a date format->

SELECT * FROM findet WHERE ISDATE(servfrom) = 0

This gave me a list of records that are not in proper date format. Now, I would like to remove them from my table. Can I use the same,

delete findet where ISDATE(servfrom)=0|||See previous post.|||That worked like a charm, thank you!!

Wednesday, March 7, 2012

DELETE where syntax ... need help :)

I have a table with the following columns,

NAME, TYPE, TAG

And there may be 'duplicates' on name and type.

How can I delete them??

I want to delete all with duplicate NAME and TYPEActually I want to delete all rows which is duplicate on NAME and
TYPE.

Name Type Tag
----------
TEST1 12 A
TEST1 12 B
TEST2 12 A
TEST4 14 B

If you take this example, I'd like to delete TEST1 and only have TEST2
and TEST4 left in my table.

This is a temporary table used to compare tables in different
databases.
I move all the tables from both databases into this temp table, and to
find the tables that are found only in on of the databases, I want to
perform the deletion as mentioned above.

The result should give me the tables (occurences) that is missing in
one of the databases. The TAG tells me which.|||Actually I want to delete all rows which is duplicate on NAME and

Quote:

Originally Posted by

TYPE.

You can remove the TAG criteria from the original statement I posted so that
all of the rows with duplicate NAME and TYPE values are removed:

CREATE TABLE dbo.PMTOOLS
(
[NAME] varchar(30) NOT NULL,
[TYPE] varchar(30) NOT NULL,
[TAG] varchar(30) NOT NULL
)
GO

INSERT INTO dbo.PMTOOLS
SELECT 'TEST1', '12', 'A'
UNION ALL SELECT 'TEST1', '12', 'B'
UNION ALL SELECT 'TEST2', '12', 'A'
UNION ALL SELECT 'TEST4', '14', 'B'
GO

DELETE dbo.PMTOOLS
FROM dbo.PMTOOLS
JOIN (
SELECT NAME, TYPE
FROM dbo.PMTOOLS
GROUP BY NAME, TYPE
HAVING COUNT(*) 1
) AS dups
ON
dups.NAME = PMTOOLS.NAME AND
dups.TYPE = PMTOOLS.TYPE

--
Hope this helps.

Dan Guzman
SQL Server MVP

"cobolman" <olafbrungot@.hotmail.comwrote in message
news:1183028881.884401.9280@.n60g2000hse.googlegrou ps.com...

Quote:

Originally Posted by

Actually I want to delete all rows which is duplicate on NAME and
TYPE.
>
Name Type Tag
----------
TEST1 12 A
TEST1 12 B
TEST2 12 A
TEST4 14 B
>
If you take this example, I'd like to delete TEST1 and only have TEST2
and TEST4 left in my table.
>
This is a temporary table used to compare tables in different
databases.
I move all the tables from both databases into this temp table, and to
find the tables that are found only in on of the databases, I want to
perform the deletion as mentioned above.
>
The result should give me the tables (occurences) that is missing in
one of the databases. The TAG tells me which.
>
>
>

|||On Jun 28, 4:50 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Actually I want to delete all rows which is duplicate on NAME and
TYPE.

>
You can remove the TAG criteria from the original statement I posted so that
all of the rows with duplicate NAME and TYPE values are removed:
>
CREATE TABLE dbo.PMTOOLS
(
[NAME] varchar(30) NOT NULL,
[TYPE] varchar(30) NOT NULL,
[TAG] varchar(30) NOT NULL
)
GO
>
INSERT INTO dbo.PMTOOLS
SELECT 'TEST1', '12', 'A'
UNION ALL SELECT 'TEST1', '12', 'B'
UNION ALL SELECT 'TEST2', '12', 'A'
UNION ALL SELECT 'TEST4', '14', 'B'
GO
>
DELETE dbo.PMTOOLS
FROM dbo.PMTOOLS
JOIN (
SELECT NAME, TYPE
FROM dbo.PMTOOLS
GROUP BY NAME, TYPE
HAVING COUNT(*) 1
) AS dups
ON
dups.NAME = PMTOOLS.NAME AND
dups.TYPE = PMTOOLS.TYPE
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"cobolman" <olafbrun...@.hotmail.comwrote in message
>
news:1183028881.884401.9280@.n60g2000hse.googlegrou ps.com...
>

Quote:

Originally Posted by

Actually I want to delete all rows which is duplicate on NAME and
TYPE.

Quote:

Originally Posted by

Name Type Tag
----------
TEST1 12 A
TEST1 12 B
TEST2 12 A
TEST4 14 B

Quote:

Originally Posted by

If you take this example, I'd like to delete TEST1 and only have TEST2
and TEST4 left in my table.

Quote:

Originally Posted by

Quote:

Originally Posted by

The result should give me the tables (occurence) that is missing in
one of the databases. The TAG tells me which.

cobolman,

I may be reading more into this than I should, but I am assuming you
want to keep one row for each set of dups. Dan's script will remove
all occurrences of the dup rows.

Do you have a sequential unique ID, or timestamp type of column on the
table? Let us know the details (schema) if you do and I'll post a
solution for you.

-- Bill|||Bill,

I do want to remove all occurences of the dup rows.
The result set should only hold the ones that did not have any dups.

Thanks to both (Dan and Bill) :)|||I guess I need help on another one as well, ...

I'd like to do a select to find all the foreign keys of a given table,
and the foreign_key columns.. (Sybase).

This SQL gives me what I want :

Select a.foreign_table_id, a.foreign_key_id, a.primary_table_id,
b.foreign_column_id, b.primary_column_id, c.column_id
from SYS.SYSFOREIGNKEY a
JOIN SYS.SYSFKCOL b ON
a.foreign_table_id = b.foreign_table_id AND
a.foreign_key_id = b.foreign_key_id
where a.foreign_table_id= XXX

But, .. what I'd really like is to instead of the column_id's and
table_id's have the actual name. I can get this from systable and
syscolumn, but I'm not sure how to write the sql|||Hmm...could this be it?

Select a.foreign_table_id, a.foreign_key_id, a.primary_table_id,
c.table_name, b.foreign_column_id,
(select column_name from sys.syscolumn where table_id =
a.foreign_table_id AND column_id = b.foreign_column_id),
b.primary_column_id,
(select column_name from sys.syscolumn where table_id =
a.primary_table_id AND column_id = b.primary_column_id)
from SYS.SYSFOREIGNKEY a
JOIN SYS.SYSFKCOL b ON
a.foreign_table_id = b.foreign_table_id AND
a.foreign_key_id = b.foreign_key_id
JOIN SYS.SYSTABLE c ON
a.primary_table_id = c.table_id
where a.foreign_table_id=XXX|||cobolman (olafbrungot@.hotmail.com) writes:

Quote:

Originally Posted by

I guess I need help on another one as well, ...
>
I'd like to do a select to find all the foreign keys of a given table,
and the foreign_key columns.. (Sybase).

You are probably better off asking in comp.databases.sybase. It does not
seem from your queries that neither Sybase use their old system
tables anymore.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Delete using 2 tables

I need to delete records in 1 table based on matching data in a 2nd table.
Below is my syntax. Is this a good syntax for this?
DELETE dbo.InternalCSIAnswers
FROM dbo.InternalCSIQuestions
WHERE (dbo.InternalCSIAnswers.InternalCSIID =
dbo.InternalCSIQuestions.InternalCSIID)
AND (dbo.InternalCSIAnswers.RepairOrderID = 26981
AND (dbo.InternalCSIQuestions.InternalType = 'E')
DavidI prefer the ANSI DELETE syntax:
DELETE FROM InternalCSIAnswers
WHERE EXISTS
(SELECT *
FROM InternalCSIQuestions AS Q
WHERE Q.InternalCSIID = InternalCSIAnswers.internalcsiid
AND Q.internaltype = 'E')
AND repairorderid = 26981 ;
This has the potential advantage of being standard SQL, unlike the
proprietary Microsoft extension you used (although the Microsoft version
often yields more efficient execution).
David Portas
SQL Server MVP
--|||DELETE dbo.InternalCSIAnswers
FROM dbo.InternalCSIAnswers a
join dbo.InternalCSIQuestions q
on q.InternalCSIID = a.InternalCSIID
WHERE a.RepairOrderID = 26981
AND q.InternalType = 'E'
"David" wrote:

> I need to delete records in 1 table based on matching data in a 2nd table.
> Below is my syntax. Is this a good syntax for this?
> DELETE dbo.InternalCSIAnswers
> FROM dbo.InternalCSIQuestions
> WHERE (dbo.InternalCSIAnswers.InternalCSIID =
> dbo.InternalCSIQuestions.InternalCSIID)
> AND (dbo.InternalCSIAnswers.RepairOrderID = 26981
> AND (dbo.InternalCSIQuestions.InternalType = 'E')
> David
>
>

Saturday, February 25, 2012

Delete syntax to delete a record from one table if a matching value isn't found in another

I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

kfrost:

Maybe something like:

DELETE FROM Broker_Rates
from Broker_Rates a
WHERE not exists
( select 0 from Broker b
where a.BID = B.id
)

Dave

|||

That appeared to do the trick. Curious, what does using the 0 in Select 0 person of your string above accomplish. I was using Select *.

Works, just curious for future reference.

Thanks.

|||

I use the zero because the select list is not relevant. Your syntax will work fine. I need to do something like "0 as dummy" so that it is more obvious that the item is a dummy item.

Dave

|||Cool. Thanks!!

Delete syntax

Can one do a 'Delete from Select' SQL Statement?
SELECT distinct Vh_make_id FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN
(select distinct Vh_make_id from marc1) AND vh_make_id != '' -- 154 Rows
I want to delete all rows from the vehicle table that have a matching
Vh_make_id in the marc1 table but also have a Vh_make_id not equal to blank?
I am

as to what exactly will occur with the following delete
statement:
DELETE FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN (SELECT distinct
Vh_make_id FROM marc1) AND vh_make_id != ''> DELETE FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN (SELECT distinct
> Vh_make_id FROM marc1) AND vh_make_id !=
You will delete every record where the vh_make_id in vht_lu_vehicle is empty
(not null, which is probably what you want) that has a vh_make_id in the
marc1 table.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
****************************************
*******
Think Outside the Box!
****************************************
*******
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:2ED21B32-3DF4-42B4-B32C-5568BBDBDDC4@.microsoft.com...
> Can one do a 'Delete from Select' SQL Statement?
> SELECT distinct Vh_make_id FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN
> (select distinct Vh_make_id from marc1) AND vh_make_id != '' -- 154 Rows
> I want to delete all rows from the vehicle table that have a matching
> Vh_make_id in the marc1 table but also have a Vh_make_id not equal to
> blank?
> I am

as to what exactly will occur with the following delete
> statement:
> DELETE FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN (SELECT distinct
> Vh_make_id FROM marc1) AND vh_make_id != ''
>

Friday, February 24, 2012

delete sql syntax for linked notes table

I am hoping this is a quick easy question for someone! :)

I am trying (struggling) with moving data from Sql Server to a Lotus
Notes table.

I am using SQL Server 2000, I have a Lotus Notes linked server (using
NotesSQL), and I wasnt to clear the table (delete all records) and then
reload it from my data on SQL Server.

What is the syntax to delete the records?

My select statement would be like this:
select * from openquery([LinkedServer], 'select * from NotesTable')
where lastName='Smith'

My delete statement ? -- cant quite figure out the syntax of this
one...
select * from openquery([LinkedServer], 'delete from NotesTable') where
lastName='Smith'
(this doesnt work)

Thanks!ProgrammerGal (carolyn_graf@.ahm.honda.com) writes:
> My delete statement ? -- cant quite figure out the syntax of this
> one...
> select * from openquery([LinkedServer], 'delete from NotesTable') where
> lastName='Smith'
> (this doesnt work)

DELETE LinkedServer...NotesTable

You may need something between the dots as well, but I don't know Lotus
Notes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

select * from [Notes_DRS_CaseName DEV].[CaseName].[dbo].[case_name]

delete from [Notes_DRS_CaseName DEV].[CaseName].[dbo].[case_name] where
CaseNum='PROD055344'

Returns:

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the
necessary interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].|||ProgrammerGal (carolyn_graf@.ahm.honda.com) writes:
> Yes, I dont think I can use this syntax.
> select * from [Notes_DRS_CaseName DEV].[CaseName].[dbo].[case_name]
> delete from [Notes_DRS_CaseName DEV].[CaseName].[dbo].[case_name] where
> CaseNum='PROD055344'
> Returns:
> Server: Msg 7312, Level 16, State 1, Line 1
> Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
> four-part name was supplied, but the provider does not expose the
> necessary interfaces to use a catalog and/or schema.
> OLE DB error trace [Non-interface error].

You should certainly not specify dbo for something in Lotus Notes,
as dbo is very SQL Server-specific.

Try one of

select * from [Notes_DRS_CaseName DEV].[CaseName]..[case_name]
select * from [Notes_DRS_CaseName DEV]...[case_name]

You could also try

delete from openquery(LinkedServer, 'SELECT * FROM ...')

although it looks completely crazy!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Here is the error from the delete...

delete from openquery([Notes_DRS_CaseName DEV],'select * from CaseName'
) where CaseNum='PROD007586'

Server: Msg 7390, Level 16, State 1, Line 1
The requested operation could not be performed because the OLE DB
provider 'MSDASQL' does not support the required transaction interface.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
returned 0x80004002].|||Hi

Try using two part names for your oracle table by adding the schema that
your oracle table is in.

John

"ProgrammerGal" <carolyn_graf@.ahm.honda.com> wrote in message
news:1145398887.423877.242610@.t31g2000cwb.googlegr oups.com...
>I tried all different versions of the selects... no luck.
> Still the same "no schema exposed message"
>
> Here is the error from the delete...
> delete from openquery([Notes_DRS_CaseName DEV],'select * from CaseName'
> ) where CaseNum='PROD007586'
> Server: Msg 7390, Level 16, State 1, Line 1
> The requested operation could not be performed because the OLE DB
> provider 'MSDASQL' does not support the required transaction interface.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
> returned 0x80004002].|||John Bell (jbellnewsposts@.hotmail.com) writes:
> Try using two part names for your oracle table by adding the schema that
> your oracle table is in.

ProgrammerGal is using LotusNotes... (Of course, I don't know LotusNotes
at all. Maybe there is an Oracle database in the bottom?)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||ProgrammerGal (carolyn_graf@.ahm.honda.com) writes:
> I tried all different versions of the selects... no luck.
> Still the same "no schema exposed message"
>
> Here is the error from the delete...
> delete from openquery([Notes_DRS_CaseName DEV],'select * from CaseName'
> ) where CaseNum='PROD007586'
> Server: Msg 7390, Level 16, State 1, Line 1
> The requested operation could not be performed because the OLE DB
> provider 'MSDASQL' does not support the required transaction interface.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
> returned 0x80004002].

I'm afraid that I'm out of ideas. Maybe you should try a LotusNotes forum,
to here if anyone in that community has been able to solve this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland

You are right I confused the ODBC OLE DB Provider MSDASQL with the Oracle
OLE DB Provider MSDAORA!

John

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AA642F4AEE2Yazorman@.127.0.0.1...
> John Bell (jbellnewsposts@.hotmail.com) writes:
>> Try using two part names for your oracle table by adding the schema that
>> your oracle table is in.
> ProgrammerGal is using LotusNotes... (Of course, I don't know LotusNotes
> at all. Maybe there is an Oracle database in the bottom?)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi

Check out http://tinyurl.com/kj3ow on how to determine how to use 4 part
naming. I am not sure if you need this now
http://www.databasejournal.com/feat...cle.php/3462011 but you
may want to check it out.

You may also want to check that this is not a read-only interface, also see
if there is a OLEDB interface available so you don't have to go through
ODBC.

John

Friday, February 17, 2012

DELETE query syntax using subqueries

I have the following SELECT query, the results of which I would delete
from the table they're pulled from:

SELECT A.* FROM SalesOrderPartPrices A
WHERE EXISTS(
SELECT 'Exists' FROM SalesOrderPartPrices B
WHERE
(A.SalesOrderNo = B.SalesOrderNo) AND
(A.PartNo = B.PartNo) AND
(A.UnitPrice = B.UnitPrice) AND
(A.RowID > B.RowID)
)

However, when I try to run the following query, I get an error ("Msg
170, Level 15, State 1, Line 1: Incorrect syntax near 'A'."):

DELETE FROM SalesOrderPartPrices A
WHERE EXISTS(
SELECT 'Exists' FROM SalesOrderPartPrices B
WHERE
(A.SalesOrderNo = B.SalesOrderNo) AND
(A.PartNo = B.PartNo) AND
(A.UnitPrice = B.UnitPrice) AND
(A.RowID > B.RowID)
)

What am I doing wrong, and how do I resolve the issue? Any help is
greatly appreciated...... thanks in advance!

-=Tek Boy=-You don't understand the SQL model for tables -- base, derived, temp,
views, etc. A correlation name acts as if it creates a new table with
the new name and the old data. That working table then disappears at
the end of the statement. If you want to change a base table, you
have to use a base table name:

DELETE FROM SalesOrderPartPrices
WHERE EXISTS(
SELECT *
FROM SalesOrderPartPrices AS S1
WHERE SalesOrderPartPrices.SalesOrderNo = S1.SalesOrderNo
AND SalesOrderPartPrices.PartNo = S1.PartNo
AND SalesOrderPartPrices.UnitPrice = S1.UnitPrice
AND SalesOrderPartPrices.RowID > S1.RowID):

Is rowid actually a PHYSICAL location in the PHYSICAL table, a la
Oracle? Are you trying to remove dups because your table was created
without keys? If so, I hope you get to run over the moron that did
this in the parking lot. You are going to have to re-do all the
reports run against this table for at least the last year because of
him. Then you have to introduce a real key, etc.

From the way you wrote your code, I would guess that SQL is not your
main language and that you are an old mainframer. You were using too
many parens and putting AND at the end of the line like we did with
punch cards.

While that kind of formatting does not mess up the compiler, it will
increase the time to maintain code by 8-10% because of the way people
read from left to right in English.|||Thanks for your reply, Joe. You are correct in your observation that
SQL is not my primarily language -- I develop mainly using ASP, HTML,
Javascript (and eventually C#/.NET). As such, my understanding of the
foundations of SQL is still rather limited -- however, I do have a
copy of "SQL for Smarties: 2nd Ed." sitting beside me, so hopefully
that situation will improve over time. As far as being an "old
mainframer", I've only read about mainframes in books and such. :)
My hands-on experience has been limited to Access, SQL Server and
(briefly) Oracle 8i.

My intention here was to clean up a bunch of data exported from a
MANMAN database, for subsequent use by a SQL Server 2000-drive web
application; no live data was harmed in the processing of these
scripts. And the only reason I added the RowID (identity) column is
because all of the other SQL Server methods I've read about for
removing duplicate rows requires some type of temp table. The most
elegant method I managed to find in the newsgroups was written for
Oracle, and made use of a RowID -- I couldn't create UNIQUE or PRIMARY
KEY constraints initially because of the duplicates. After I cleaned
everything up, I deleted the RowID column and created a PRIMARY KEY
constraint on (SalesOrder, PartNo).

Since you mention code formatting -- is there a method you DO suggest?
I haven't found a clearly-documented method of writing scripts that I
find easy to read AND doesn't take up inordinate amount of real estate
on my monitor. Always looking for a better way to do
things.........

-=Tek Boy=-|||Speaking of formatting, if anybody from MS is listening, I would LOVE
to be able to set the width of the tab in Query Analyzer as is
possible in the VS IDE.

On 11 Feb 2004 13:32:22 -0800, vejitaasp@.hotmail.com (Saiyan Vejita)
wrote:

>Thanks for your reply, Joe. You are correct in your observation that
>SQL is not my primarily language -- I develop mainly using ASP, HTML,
>Javascript (and eventually C#/.NET). As such, my understanding of the
>foundations of SQL is still rather limited -- however, I do have a
>copy of "SQL for Smarties: 2nd Ed." sitting beside me, so hopefully
>that situation will improve over time. As far as being an "old
>mainframer", I've only read about mainframes in books and such. :)
>My hands-on experience has been limited to Access, SQL Server and
>(briefly) Oracle 8i.
>My intention here was to clean up a bunch of data exported from a
>MANMAN database, for subsequent use by a SQL Server 2000-drive web
>application; no live data was harmed in the processing of these
>scripts. And the only reason I added the RowID (identity) column is
>because all of the other SQL Server methods I've read about for
>removing duplicate rows requires some type of temp table. The most
>elegant method I managed to find in the newsgroups was written for
>Oracle, and made use of a RowID -- I couldn't create UNIQUE or PRIMARY
>KEY constraints initially because of the duplicates. After I cleaned
>everything up, I deleted the RowID column and created a PRIMARY KEY
>constraint on (SalesOrder, PartNo).
>Since you mention code formatting -- is there a method you DO suggest?
> I haven't found a clearly-documented method of writing scripts that I
>find easy to read AND doesn't take up inordinate amount of real estate
>on my monitor. Always looking for a better way to do
>things.........
>
>-=Tek Boy=-|||>> My intention here was to clean up a bunch of data exported from a
MANMAN database, .. <<

Okay, this is not **real** data, but only a scrubbing table! Have you
looked at any ETL tools for this job? They are geared for this kind
of thing and havea lot of extras that can be helpful. Otherwise, use
IDENTITY or a sequential file for the scrub work.

>> Since you mention code formatting -- is there a method you DO
suggest? <<

My rules have been picked up by my various publishers, and they are:

1) Uppercase keywords and put one per line
2) lowercase scalars, like column names, local variables, etc.
3) Capitalize schema objects like table names, view names, etc.
4) Indent along a gutter for each subquery or clause

And follow ISO-11179 Standard nbaming conventions.|||In Query Analyzer, open Tools | Options then on the Editor tab see Tab size
(in spaces). Is that what you meant?

"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
news:p0a03050ott4cf0claq65cfoi8b1v0oco7@.4ax.com...
> Speaking of formatting, if anybody from MS is listening, I would LOVE
> to be able to set the width of the tab in Query Analyzer as is
> possible in the VS IDE.
> On 11 Feb 2004 13:32:22 -0800, vejitaasp@.hotmail.com (Saiyan Vejita)
> wrote:
> >Thanks for your reply, Joe. You are correct in your observation that
> >SQL is not my primarily language -- I develop mainly using ASP, HTML,
> >Javascript (and eventually C#/.NET). As such, my understanding of the
> >foundations of SQL is still rather limited -- however, I do have a
> >copy of "SQL for Smarties: 2nd Ed." sitting beside me, so hopefully
> >that situation will improve over time. As far as being an "old
> >mainframer", I've only read about mainframes in books and such. :)
> >My hands-on experience has been limited to Access, SQL Server and
> >(briefly) Oracle 8i.
> >My intention here was to clean up a bunch of data exported from a
> >MANMAN database, for subsequent use by a SQL Server 2000-drive web
> >application; no live data was harmed in the processing of these
> >scripts. And the only reason I added the RowID (identity) column is
> >because all of the other SQL Server methods I've read about for
> >removing duplicate rows requires some type of temp table. The most
> >elegant method I managed to find in the newsgroups was written for
> >Oracle, and made use of a RowID -- I couldn't create UNIQUE or PRIMARY
> >KEY constraints initially because of the duplicates. After I cleaned
> >everything up, I deleted the RowID column and created a PRIMARY KEY
> >constraint on (SalesOrder, PartNo).
> >Since you mention code formatting -- is there a method you DO suggest?
> > I haven't found a clearly-documented method of writing scripts that I
> >find easy to read AND doesn't take up inordinate amount of real estate
> >on my monitor. Always looking for a better way to do
> >things.........
> >-=Tek Boy=-|||Actually I misspoke, I didn't mean Query Analyzer, I meant the
workspace for writing a stored procedure over in Enterprise Manager.

On Fri, 07 May 2004 16:30:43 GMT, "tperovic" <tonyperovic@.yahoo.com>
wrote:

>In Query Analyzer, open Tools | Options then on the Editor tab see Tab size
>(in spaces). Is that what you meant?
>"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
>news:p0a03050ott4cf0claq65cfoi8b1v0oco7@.4ax.com...
>> Speaking of formatting, if anybody from MS is listening, I would LOVE
>> to be able to set the width of the tab in Query Analyzer as is
>> possible in the VS IDE.
>>
>> On 11 Feb 2004 13:32:22 -0800, vejitaasp@.hotmail.com (Saiyan Vejita)
>> wrote:
>>
>> >Thanks for your reply, Joe. You are correct in your observation that
>> >SQL is not my primarily language -- I develop mainly using ASP, HTML,
>> >Javascript (and eventually C#/.NET). As such, my understanding of the
>> >foundations of SQL is still rather limited -- however, I do have a
>> >copy of "SQL for Smarties: 2nd Ed." sitting beside me, so hopefully
>> >that situation will improve over time. As far as being an "old
>> >mainframer", I've only read about mainframes in books and such. :)
>> >My hands-on experience has been limited to Access, SQL Server and
>> >(briefly) Oracle 8i.
>>> >My intention here was to clean up a bunch of data exported from a
>> >MANMAN database, for subsequent use by a SQL Server 2000-drive web
>> >application; no live data was harmed in the processing of these
>> >scripts. And the only reason I added the RowID (identity) column is
>> >because all of the other SQL Server methods I've read about for
>> >removing duplicate rows requires some type of temp table. The most
>> >elegant method I managed to find in the newsgroups was written for
>> >Oracle, and made use of a RowID -- I couldn't create UNIQUE or PRIMARY
>> >KEY constraints initially because of the duplicates. After I cleaned
>> >everything up, I deleted the RowID column and created a PRIMARY KEY
>> >constraint on (SalesOrder, PartNo).
>>> >Since you mention code formatting -- is there a method you DO suggest?
>> > I haven't found a clearly-documented method of writing scripts that I
>> >find easy to read AND doesn't take up inordinate amount of real estate
>> >on my monitor. Always looking for a better way to do
>> >things.........
>>>>> >-=Tek Boy=-
>

Thursday, March 29, 2012

Deleting SP syntax check please...

Deleting SP syntax check please...

deleting rows based on nvarchar data

Wednesday, March 7, 2012

DELETE where syntax ... need help :)

Delete using 2 tables

Saturday, February 25, 2012

Delete syntax to delete a record from one table if a matching value isn't found in another

Delete syntax

Friday, February 24, 2012

delete sql syntax for linked notes table

Friday, February 17, 2012

DELETE query syntax using subqueries

DENY permission,SQL server

Blog Archive

About Me