CODEWITHSUNDEEP
mysqlsql-update
Tarek Fellah
Tarek Fellah

Reputation: 179

Sql update with where not in

I have this information in table ps_product_lang:

id_product  id_lang
1           1  
1           2
1           3
2           1
3           1
4           1
5           1
5           2
5           3

the output should be 

id_product  id_lang
1           1  
1           2
1           3
2           2
3           2
4           2
5           1
5           2
5           3

I would like to update the record which has an unique id_product and the id_lang should be 1 that's mean that concern the 4th , 5th and 6th record:

I tried this query but it doesn't work

UPDATE `ps_product_lang` p 
SET    p.`id_lang` = '2' 
WHERE  p.`id_lang` = '1' 
       AND p.id_product NOT IN (SELECT `id_product` 
                                FROM   `ps_product_lang` ps 
                                WHERE  p.id_product = ps.id_product 
                                       AND ps.id_lang = '2');

Upvotes: 2

Views: 14388

Answers (4)

Mihai
Mihai

Reputation: 26784

UPDATE `ps_product_lang` p 
SET    p.`id_lang` = '2' 
WHERE  p.`id_lang` = '1' 
AND id_product IN(SELECT id_product FROM
(SELECT  id_product FROM ps_product_lang GROUP BY    id_product HAVING COUNT(*)=1 )as x)

SQL fiddle

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

do this as you make , but just make an inner select .

 UPDATE `ps_product_lang`  
 SET    `id_lang` = '2' 
 WHERE  `id_lang` = '1' 
   AND id_product NOT IN (select `id_product` from (SELECT `id_product` 
                            FROM   `ps_product_lang`  
                            WHERE  id_product = id_product 
                                   AND id_lang = '2')t );

demo here

Upvotes: 2

Filipe Silva
Filipe Silva

Reputation: 21657

You can try this:

UPDATE `ps_product_lang` p
INNER JOIN (
  SELECT id_product
  FROM ps_product_lang
  GROUP BY id_product
  HAVING COUNT(*) = 1
  ) b ON b.id_product = p.id_product
SET p.`id_lang` = '2'
WHERE p.`id_lang` = '1'

sqlfiddle demo

The inner query gets you only the rows with unique product_id and you UPDATE the ones that have id_lang = 1.

Upvotes: 3

Ulrich Horus
Ulrich Horus

Reputation: 350

you have a contradiction in your request:

p.id_product not in

against 

p.id_product= ps.id_product

Upvotes: 0

Related Questions