Error is query

Is this SQL Server? If so, you should use % and not *:

Delete COSTCTR
 Where COSTCTR.COSTCENTER not like '%CAP%' or
 COSTCTR.COSTCENTER not like '%K%' or
 COSTCTR.DESCRIPTION not like '%A/%'

Select allOpen in new window

Hi,
Please try below-

Delete a
FROM COSTCTR a
Where 
a.COSTCENTER not like 'CAP%' or
a.COSTCENTER not like 'K%' or
a.DESCRIPTION not like 'A/%'

Select allOpen in new window

One more option is there apart from my last comment.

--

DELETE a
FROM COSTCTR a
WHERE CHARINDEX( '%CAP%', a.COSTCENTER , 0) = 0 
OR CHARINDEX( '%K%', a.COSTCENTER , 0) = 0 
OR CHARINDEX( '%A/%', a.DESCRIPTION , 0) = 0 

--

Select allOpen in new window

Hope it helps!

please clarify indeed the db type.
anyhow, the message indicates that you are trying to delete records which are referenced in other tables.
in which case you need to either update or delete those records first

If you are using MS ACCESS SQL, then please try like below-

--
DELETE FROM COSTCTR
WHERE COSTCTR.COSTCENTER NOT LIKE "*CAP*" or COSTCTR.COSTCENTER NOT LIKE "*K*" or COSTCTR.DESCRIPTION NOT LIKE "*A/*"
--

Select allOpen in new window

Hope it helps!

hi
There is some conflict where is says data exists in the table EQUIP
I cannot see the conflict, any ideas how to find this

Gordon

Which DB are you using ? SQL SERVER /MS ACCESS?

please show the foreign key between the two tables. from the names of the table an equip (team) is assigned to costcenter, and you are trying to delete a cost center that teams are assigned to

There is some conflict where is says data exists in the table EQUIP
 I cannot see the conflict, any ideas how to find this

I guess the table has a trigger for delete that's accessing the EQUIP table.

The error message is
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_EQUIP_COSTCENTER". The conflict occurred in database "MP2LIVE", table "dbo.EQUIP", column 'COSTCENTER'.
The statement has been terminated.

But looking at the uniques vales in that table I cannot see the conflict as the EQUIP table has either CAP% or values where the description stats with A/ (all the xxxx xx values have this A/ in the description in the costctr table

8305 120
8307 210
8307 170
8313 140
NULL
CAPEX11-073
8730 140
8307 140
8307 120
8305 140
8313 130
8307 230
CAPEX14-014
8307 110
8307 130
CAPEX14-017
8309 170
8305 170
8305 130

This is in MS SQL

change OR by AND , and use % instead of *

It works OK with the and
Well done