CODEWITHSUNDEEP
mysqlsql
user2813853
user2813853

Reputation:

update and 2 select statements in same query causing an error

I have a table with a field c_id which has the entries with some values of CustomerIds. I need to set those to null if those customer Ids are not valid as per the table.

I am using the following query. But it seems to throw an error:

update Customers set c_id=NULL where customer_id in (select customer_id from Customers where c_id not in (select customer_id from Customers);

Could someone help me identify the problem here

Upvotes: 0

Views: 66

Answers (2)

Arulkumar
Arulkumar

Reputation: 13237

I avoided IN and used JOINS to fine tune your code:

UPDATE CUST SET C_Id = NULL 
FROM Customers CUST
LEFT JOIN Customers LJC ON LJC.Customer_Id = CUST.C_Id
WHERE LJC.Customer_Id IS NULL

SQL Fiddle: http://sqlfiddle.com/#!3/59fb1/5

I'm explain what I have done:

Created dummy table and data for Customers table:

CREATE TABLE Customers (
    Customer_Id  INT,
    C_Id    INT
)

INSERT INTO Customers
SELECT 1, 11 UNION
SELECT 2, 22 UNION
SELECT 22, 3 UNION 
SELECT 11, 4 UNION
SELECT 5, 6 UNION
SELECT 7, 8 UNION
SELECT 3, 9

Here 11, 22 and 3 are exists in Customer_Id, C_Id, So as per your request the other C_Id 4, 6, 8 and 9 those are not exist in Customer_Id are will UPDATE as NULL.

The below block will return the C_Id those are not exists in the Customer_Id

SELECT C1.C_Id
FROM Customers C1
LEFT JOIN Customers C2 ON C2.Customer_Id = C1.C_Id
WHERE C2.Customer_Id IS NULL

The below block will update C_Id as NULL from the above SELECT block

UPDATE Customers SET C_Id = NULL 
WHERE C_Id IN (
    SELECT C1.C_Id
    FROM Customers C1
    LEFT JOIN Customers C2 ON C2.Customer_Id = C1.C_Id
    WHERE C2.Customer_Id IS NULL
)

From the above block, if I remove the IN and modify using JOIN, the query what I gave in the top will come.

Upvotes: 1

ashishmaurya
ashishmaurya

Reputation: 1196

You have a syntax error.

Add ) at the end of query for closing first select query

update Customers set c_id = NULL 
where customer_id in 
    (select customer_id 
     from Customers 
     where c_id not exists 
     (select customer_id from Customers)
    );

Upvotes: 0

Related Questions