CODEWITHSUNDEEP
mysqlselectsql-update
The50
The50

Reputation: 1178

SQL Update for Selected rows

I have this SQL Code:

SELECT * FROM `clients_branches` 
         WHERE NULLIF(clients_branches.invoice_email, '') IS NULL 
         GROUP BY client_code 
         HAVING COUNT(*) = 1

It returns all rows which appears only once in the database, also it returns only the ones with no email set. Now I need to apply UPDATE function to all of this select statement. How could I do it? I need to set clients_branches.invoice_send to 0 for all these rows.

I can't seem to use HAVING COUNT on UPDATE statement like this:

UPDATE `clients_branches` 
SET clients_branches.invoice_send = 0 
WHERE NULLIF(clients_branches.invoice_email, '') IS NULL
HAVING COUNT(*) = 1

Without HAVING COUNT I will change all of the rows which repeats at least once in this table. And I need to change only the ones with count = 1.

Upvotes: 0

Views: 644

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133410

You could use a join for allow the use of the table for update and the result of your query 

  update `clients_branches` 
  JOIN 
  (  
    select client_code, count(*)
    FROM `clients_branches` 
    WHERE NULLIF(clients_branches.invoice_email, '') IS NULL 
    group by client_code 
    HAVING COUNT(*) = 1 
  ) t on t.client_code = `clients_branches`.client_code
  set clients_branches.invoice_send =  0
  ;

Upvotes: 1

Related Questions