CODEWITHSUNDEEP
mysql
DeveloperLV
DeveloperLV

Reputation: 1781

MySQL update null values where product code has value

I have this query here:

select * from warehouselog where productDescription is null;

It returns me this:

ProductCode     productDescription
PKFIL11014      null
PKOUT15012      null
PKLAB14016      null
PKLAB14005      null
PKPOL17024      null

I know there is data out there with these product codes with some data in the same table.

e.g.

PKLAB14005      blue pen
PKPOL17024      black pen

How can I update the table where productDescription is null and set productDescription to a value where the ProductCode has data? It doesn't need to be unique.

Upvotes: 1

Views: 101

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94904

Use a correlated subquery, assigning the minimum or maximum value found for the product. It doesn't matter whether you choose MIN or MAX, both will look for a value that is not null. Here is an example with MIN:

update warehouselog w
set productdescription =
(
  select min(productdescription)
  from (select * from warehouselog) w2
  where w2.productcode = w.productcode
)
where productdescription is null;

(from (select * from warehouselog) w2 instead of a mere from warehouselog w2 is necessary in MySQL, because it doesn't allo the updated table to be used directly in a subquery.)

Upvotes: 2

Related Questions