How to delete rows in tables...

sdj_dk

I need to delete some rows in some of my tables after tranfering data from my OLTP to SQL database.

Im using SQL 2000

I have tried with the following:

Delete from fsalesinvoiceline

Join dsalesinvoiceheader on

Fsalesinvoiceline.salesid= dsalesinvoiceheader.salesid and

Fsalesinvoiceline.company= dsalesinvoiceheader.company

Where dsalesinvoiceheader.billtocustomerno=¡¯INDTAST DEBITORNUMMER¡¯

Go

Delete from dsalesinvoiceheader

Where dsalesinvoiceheader.billtocustomerno=¡¯INDTAST DEBITORNUMMER¡¯

 

I get the following error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'JOIN'

 

What am I doing wrong

/Soren D. Jensen

Prakash Paramasivam

Hello,

You could try to use Microsoft SQL Server Management Studio to open the table and try to delete frm the UI.

Thanks,

Barry Andrew

sdj_dk wrote:

I need to delete some rows in some of my tables after tranfering data from my OLTP to SQL database. Im using SQL 2000 I have tried with the following: Delete from fsalesinvoiceline Join dsalesinvoiceheader on Fsalesinvoiceline.salesid= dsalesinvoiceheader.salesid and Fsalesinvoiceline.company= dsalesinvoiceheader.company Where dsalesinvoiceheader.billtocustomerno=¡¯INDTAST DEBITORNUMMER¡¯ Go Delete from dsalesinvoiceheader Where dsalesinvoiceheader.billtocustomerno=¡¯INDTAST DEBITORNUMMER¡¯ I get the following error message: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'JOIN' What am I doing wrong /Soren D. Jensen

BE ULTRA CAREFUL!!! - I would suggest a backup or at least a test on a staging system if available.

DELETE
FROM
fsalesinvoiceline, dsalesinvoiceheader
WHERE
fsalesinvoiceline.salesid = dsalesinvoiceheader.salesid
AND dsalesinvoiceheader.billtocustomer = 'INDTAST DEBITORNUMMER'

sdj_dk

Barry Andrew wrote:

BE ULTRA CAREFUL!!! - I would suggest a backup or at least a test on a staging system if available. DELETE FROM fsalesinvoiceline, dsalesinvoiceheader WHERE fsalesinvoiceline.salesid = dsalesinvoiceheader.salesid AND dsalesinvoiceheader.billtocustomer = 'INDTAST DEBITORNUMMER'

Like I said the data in these tables are copies from our OLTP database (MBS Navision) they are emptied every night and filled with new data, so nothing will get lost if something goes wrong. I will try your suggestion tomorrow.

sdj_dk

I have another question on how to delete specific rows from a table.

The table is called ProdTaskLine

I need to delete rows where:

[Cost Type] have the value 1

[Item Type] have the value 1

[Line Type] have the value 0

And last but not least the first character in the field [Cost No] have to be different from the letter 'B'

I am a total newbie to this and have no idear how to code this.

Barry Andrew

Ok we can work this out also. But has the above solved your original posts problem

sdj_dk

Barry Andrew wrote:

Ok we can work this out also. But has the above solved your original posts problem

I haven't had the time to look at it yet - had a datetime error this morning that I need to solve first so we can use our cubes. :) I will post again when I have tested your surgestion.

sdj_dk

It seems like your are trying to dele from both tables at once. When doing this I get this error message:

Line 1: Incorrect syntax near ','

I then tried only deleting from fSalesInvoiceLine with this code:

DELETE FROM fSalesInvoiceLine
WHERE (fsalesinvoiceline.salesid = dsalesinvoiceheader.salesid) AND (Fsalesinvoiceline.company= dsalesinvoiceheader.company) AND (dsalesinvoiceheader.billtocustomerno = 40000)

This give the following error message:

The column prefix 'dsalesinvoiceheader' does not match with a table name or alias name used in the query

Have no idear what to do next I have checked all the table and column names.

Barry Andrew

ok lets just try,

DELETE f, d
FROM fsalesinvoiceline AS f, dsalesinvoiceheader AS d
WHERE
f.salesid = d.salesid
AND d.billtocustomer = 'INDTAST DEBITORNUMMER'

sdj_dk

Barry Andrew wrote:

ok lets just try, DELETE f, d FROM fsalesinvoiceline AS f, dsalesinvoiceheader AS d WHERE f.salesid = d.salesid AND d.billtocustomer = 'INDTAST DEBITORNUMMER'

Still get this error message: "Line 1: Incorrect syntax near ',' "

Am I doing this right To test the code I use SQL Server Enterprice Manager - right click on the table fSalesInvoiceLine and choose Query...

Jens K. Suessmeyer

YOu can only delete from one table at a time. So either specify fsalesinvoiceline or dsalesinvoiceheader.

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Barry Andrew

Hi Jens Suessmeyer,

I thought that providing we structure the query correctly, a delete with a join was permitted

Im working on this off the following reference; http://msdn2.microsoft.com/en-us/library/aa258847(SQL.80).aspx

Barry Andrew

Barry Andrew wrote:

ok lets just try, DELETE f, d FROM fsalesinvoiceline AS f, dsalesinvoiceheader AS d WHERE f.salesid = d.salesid AND d.billtocustomer = 'INDTAST DEBITORNUMMER'

Can we finally try;

DELETE
fsalesinvoiceline
FROM
fsalesinvoiceline
INNERJOIN
dsalesinvoiceheader ON fsalesinvoiceline.salesid = fsalesinvoiceline.salesid
WHERE
dsalesinvoiceheader.billtocustomer = 'INDTAST DEBITORNUMMER'

It looks bizzarre, but I think it looks like this should work.

Barry Andrew

Jens K. Suessmeyer

Sure it is, but the query you pointed out below is finally not correct:

DELETE
fsalesinvoiceline
FROM
fsalesinvoiceline
INNER JOIN
dsalesinvoiceheader ON fsalesinvoiceline.salesid = dsalesinvoiceheader.salesid
--(guess you wanted to join with the dsalesinvoiceheader)
WHERE
dsalesinvoiceheader.billtocustomer = 'INDTAST DEBITORNUMMER'


HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Barry Andrew

Jens K. Suessmeyer wrote:

Sure it is, but the query you pointed out below is finally not correct: DELETE fsalesinvoiceline FROM fsalesinvoiceline INNER JOIN dsalesinvoiceheader ON fsalesinvoiceline.salesid = dsalesinvoiceheader.salesid --(guess you wanted to join with the dsalesinvoiceheader) WHERE dsalesinvoiceheader.billtocustomer = 'INDTAST DEBITORNUMMER' HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de ---

Ah I see now! doh!

thanks for pointing it out