curiousk curiousk - 4 months ago 7
SQL Question

Delete specific rows that can have null values in some columns

This is the query I am trying to execute in MySQL:

DELETE from Connection
where
(sourceIp , destinationIp,
destinationPort,
sourceServerId,
destinationServerId,
sourceProcessId,
destinationProcessId) IN (Select
sourceIp,
destinationIp,
destinationPort,
sourceServerId,
destinationServerId,
sourceProcessId,
destinationProcessId
from
(Select
sourceIp,
destinationIp,
destinationPort,
sourceServerId,
destinationServerId,
sourceProcessId,
destinationProcessId
from
Connection

where
sourceIp = '12.43.34.53'
and destinationIp = '12.43.34.65'
and destinationPort = '3306'
and ((sourceServerId = '1'
and destinationServerId = '2'
and sourceProcessId = '1'
and destinationProcessId = '2')
or (sourceServerId IS NULL
and destinationServerId = '2'
and destinationProcessId = '2')
or (sourceServerId = '1'
and sourceProcessId = '1'
and destinationServerId is NULL))) as C);


I am doing multiple selects because I cannot specify same target table in from Clause. The above query executes. My table looks like this :


  1. sourceIp, destinationIp, destinationPort, sourceServerId, destinationServerId, sourceProcessId, destinationProcessId
    '12.43.34.53', '12.43.34.65', '3306', NULL, '2', NULL, '2'

  2. sourceIp, destinationIp, destinationPort, sourceServerId, destinationServerId, sourceProcessId, destinationProcessId
    '12.43.34.53', '12.43.34.65', '3306', '1', NULL, '1', NULL

  3. sourceIp, destinationIp, destinationPort, sourceServerId, destinationServerId, sourceProcessId, destinationProcessId
    '12.43.34.53', '12.43.34.65', '3306', '1', '2', '1', '2'



The above query only deletes the last row( #3). But if cut the query at IN and execute the rest as Select , I get all 3 rows. Is it because IN with null values does not work? Any suggestions on how I can modify this query to make it delete all the 3 rows.
(all the ids and ips, and destination port together make the rows unique in the table)

Answer

wow you don't need any of those selects and using IN in this capacity will NOT give you the results you want if any of those fields return a NULL.......... Your problem is with your WHERE statement.

Cleaning up above you could simplify to:

DELETE from Connection 
where
    sourceIp = '12.43.34.53'
    and destinationIp = '12.43.34.65'
    and destinationPort = '3306'
    and ((sourceServerId = '1'
    and destinationServerId = '2'
    and sourceProcessId = '1'
    and destinationProcessId = '2')
    or (sourceServerId IS NULL
    and destinationServerId = '2'
    and destinationProcessId = '2')
    or (sourceServerId = '1'
    and sourceProcessId = '1'
    and destinationServerId is NULL))
;

The issue of why you are not deleting the correct records is because of the order of precedence of your conditions in the WHERE clause. I am not going to attempt to unravel this because your post needs some work such as example data and desired result as I am certain there will be a better way to write your query.

Comments