CODEWITHSUNDEEP
mysqlsql
Raimondas Kazlauskas
Raimondas Kazlauskas

Reputation: 249

MYSQL update join is null

I was wondering can I change product status to 0 if product_id has't assigned any category_id in other table.

+------------+----------+  +------------+-------------+
| Product_id |  Status  |  | Product_id | cateogry_id |
+------------+----------+  +------------+-------------+
|      1     |     1    |  |     1      |      10     |
|      2     |     1    |  |     3      |      20     |
|      3     |     1    |  +------------+-------------+
+------------+----------+

In result i need that product_id = 2 which don't have category status be 0. Is there one MySQL query for that?

Upvotes: 0

Views: 1119

Answers (3)

ssn
ssn

Reputation: 509

Or even more simple.. if it is not found in table2 at all:

Update table1
Set status = 0
Where Product_id not in (SELECT Product_id FROM table2)

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25862

Simple first you need to get the rows that arent in the table..

table1 is the table with the status
table2 is the table with the category id

           table1                     table2
+------------+----------+  +------------+-------------+
| Product_id |  Status  |  | Product_id | cateogry_id |
+------------+----------+  +------------+-------------+
|      1     |     1    |  |     1      |      10     |
|      2     |     1    |  |     3      |      20     |
|      3     |     1    |  +------------+-------------+
+------------+----------+

so now run this query to get the rows that don't have a category_id

SELECT product_id 
FROM table1 t
LEFT JOIN table2 t2 ON t2.product_id = t.product_id
WHERE t2.product_id IS NULL

now update table1 like so

UPDATE table1 t,
(   SELECT product_id 
    FROM table1 t
    LEFT JOIN table2 t2 ON t2.product_id = t.product_id
    WHERE t2.product_id IS NULL
) t1
SET t.status = 0
WHERE t1.product_id = t.product_id

DEMO

Upvotes: 6

M Khalid Junaid
M Khalid Junaid

Reputation: 64486

You can use left join in update with is null

update product p
left join product_relation pr on (p.Product_id = pr.Product_id)
set p.Status = 0
where pr.Product_id is null

Demo

Upvotes: 2

Related Questions