There is a problem when two table query is nested

Other SQL Server Topics
1

Hi everyone,

I have a query problem;

i have 2 table. Table1 and Table2
(ip = Internet Protocol - IP Address)

Select Query working but Update query not working

Select Query:

SELECT
        TABLE1.ip
    FROM
        TABLE1
    WHERE NOT EXISTS(
        SELECT
            *
        FROM
            TABLE2
        WHERE
            TABLE1.ip = TABLE2.ip
    )
	AND TABLE1.ip_usage = 1 AND TABLE1.ip_type = 'D'

Update Query: (not working - number of rows affected 0)

UPDATE
    TABLE1
SET
    ip_usage = '0'
WHERE NOT
	EXISTS(
	SELECT
		*
	FROM
		TABLE2
	WHERE
		TABLE1.ip = TABLE2.ip 
) AND TABLE1.ip_usage = 1 AND TABLE1.ip_type = 'D'

Table1 = IP_POOL and Table2 Live_Using_Ip_Address

I want to clear the unused ip addresses in the table

Please help, thanks

2

how about this?

UPDATE t1
SET    t1.ip_usage = '0'
FROM   table1 t1
       LEFT JOIN table2 t2
              ON t1.ip = t2.ip
WHERE  t1.ip_usage = 1
       AND t1.ip_type = 'D'
       AND t2.ip IS NULL
1 Like
3

it's worked thanks :pray: