How to delete rows in tables...

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?

/S?ren D. Jensen

Hello,

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

Thanks,

|||

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?

/S?ren 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'

|||

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.

|||

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.

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

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.

|||

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.

|||

ok lets just try,

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

|||

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...

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

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

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

|||

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

|||

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