473,320 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Delete query with IN clause

67 64KB
Hi,

I have a problem on deleting data from table with query which where clause use NOT IN. my query is:
Expand|Select|Wrap|Line Numbers
  1. DELETE * FROM role WHERE role_name not in ('MOC_PP',' MOC_SR',' MOC_BB')
but the result just leave only one data row with role_name = 'MOC_PP'.

Could anybody tell me what is the problem with my query?

Sophanna
Sep 5 '14 #1

✓ answered by MikeTheBike

Hi

Just guessing, but I suspect you have an extra space in two of the IN list, ie I think it pehaps should be like this
Expand|Select|Wrap|Line Numbers
  1. DELETE * FROM role WHERE role_name not in ('MOC_PP','MOC_SR','MOC_BB')
In your query it would delete all record with with a role_name of MOC_SR and MOC_BB


MTB

2 1031
MikeTheBike
639 Expert 512MB
Hi

Just guessing, but I suspect you have an extra space in two of the IN list, ie I think it pehaps should be like this
Expand|Select|Wrap|Line Numbers
  1. DELETE * FROM role WHERE role_name not in ('MOC_PP','MOC_SR','MOC_BB')
In your query it would delete all record with with a role_name of MOC_SR and MOC_BB


MTB
Sep 5 '14 #2
sophannaly
67 64KB
Hi MTB,

Thanks you so much, now I could figure out where the problem is.

Best regards,
Sophanna
Sep 5 '14 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: r | last post by:
I am trying to do a delete query like this one: DELETE FROM pending WHERE pages.url=pending.url AND pages.date=pending.date; If I run it (via PHP) I get the following error: Unknown table...
2
by: Joe Del Medico | last post by:
I have two tables A & B and I want to delete all the records in A that are not in B. Can I do this in the query builder? It seems like a simple problem. I can easily find the records in A that...
5
by: Mike Wiseley | last post by:
I would like to create a delete query that consists of two tables linked together. One table will be the target for the deletions while the other table's equi-joins will tell the delete query which...
1
by: Mark Reed | last post by:
Hi All, I have a table with a date field and 6 number fields. The number fields are not in numerical order across the fields and I want number 1 to be the lowest number and number 6 to be the...
6
by: Mark Reed | last post by:
Hi all, Please help. I have a table with 2 fields of which I am trying to change a select query into a delete query. the select query is: SELECT Table1.Date, Min(Table1.Ball) AS MinOfBall...
2
by: Dalan | last post by:
Having searched the archives regarding a Delete Query, I found nothing specific to my need, although there seems to be a plethora of ideas and suggestions on queries in general. I have used Delete...
13
by: forbes | last post by:
Hi, I have a user that used the Query Wizard to create a query in Access. Now she claims that her master table is missing all the data that was excluded from the query. Can you create anything...
1
by: shades234 | last post by:
I was wondering if you can run a delete query in one database, and have the action take place in another database. Namely, do something like Delete (fields) IN (Database location) FROM (specify...
3
by: Kevin M | last post by:
I have one table and have created a form from that table. Also, I have created a delete query. I want to but a button on the form which will delete all records from the table; however, I cannot get...
3
by: rbukkara | last post by:
Hi guys, I have some trouble with the following query. Please look into this and lemme know the solution ASAP. It certainly involves aggregations and the 'having clause' BROKER( ID integer...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.