asked on

create a stored procedure for delete rows

Hello,

I try to create a stored procedure for delete rows with the following condition :

The procedure must remove all lines with as livSlipNumber = 0 or "" or "null" if there is only the same line ( but not equal livSlipNumber 0, "" or nul)

Herewith the table :

CREATE TABLE [dbo].[salesorder](
      [livSlipNumber] [nvarchar](20) NOT NULL,
      [SalesOrderNumber] [numeric](20, 0) NOT NULL,

) ON [PRIMARY]

GO

Thanks

Regards

tim_cs

I'm not really sure what you're asking for. Can you provide a data example of the data in the table and what the results would be after the proc runs?

Angelp1ay

Do you mean it should delete the rows only if there is another record with the same SalesOrderNumber which has non-null values?

SELECT * FROM salesorder s1
-- DELETE FROM salesorder s1 
INNER JOIN salesorder s2 
    ON s1.SalesOrderNumber = s2.SalesOrderNumber
    AND (s1.livSlipNumber = 0 OR s1.livSlipNumber = "" OR s1.livSlipNumber = "null")
    AND (s2.livSlipNumber <> 0 AND s2.livSlipNumber <> "" AND s2.livSlipNumber <> "null")

Open in new window

Does this produce the list of rows you want deleted?
Try running the select first (with the delete left commented) to check and if you're happy then swap the comment to the first line.

bibi92

ASKER

Exactly I mean it should delete the rows only if there is another record with the same SalesOrderNumber which has non-null values.

Thanks

SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Angelp1ay

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jogos

The is null in the delete is necessary. In the exists-part it can be left out.

Angelp1ay

The column has a not null constraint so it's impossible for there to be any nulls.
Why do you say it is necessary?

jogos

You are right

Angelp1ay

Thanks bibi.

I hope you don't mind but I asked the admin if this could be made a 250-250 split with jogos since we got to the final answer by bouncing off each other. Sharing is caring ;)

Have a great day both! :)

jogos

Thx Angelp1ay. It is often overseen the split. But I can live with that.

Angelp1ay

@SouthMod - Move 250pts to ID: 39789138 as Assist, leaving current answer with the other 250.