Hey guys a bit of a novice question.

I have a table with the following inputs

Location in row 1 and row 4 in the table are the same however row 1 is missing code which should also be A.

i.e. the desired results should be

I tried

UPDATE sales AS dst
    SET dst.code = src.code
    FROM sales AS src
    WHERE dst.location = src.location 
    AND dst.location <> '' AND src.location = ''
        ;

but to no avail

CodePudding user response：

I'm almost hesitant to offer this up because it assumes a lot, but just to answer your specific question, I think this is what you want:

UPDATE sales AS dst
SET code = src.code
FROM sales AS src
WHERE
  dst.location = src.location AND
  dst.code is null and
  src.code is not null

I think in your code you mixed up code and location, so on the last two lines you used "location" when you meant "code."

In addition you are likely confusing nulls with empty spaces. I assume the data is null and not containing an empty space. It's possible that assumption is wrong.

Also, don't use the alias on the set. It's implicit.

CodePudding user response：

here you have two possibilities how to resolve your problem

CREATE TABLe sales (location int, code varchar(10))

INSERT INTO sales VALUEs (1,NULL),(2,'B'),(3,'c'),(1,'A')

4 rows affected

UPDATE sales s1
SET code = (SELECT MIN(code) FROM sales s2 WHERE s1.location = s2.location AND s2.location IS NOT NULL)

4 rows affected

SELECT * FROM sales

location | code
-------: | :---
       1 | A   
       2 | B   
       3 | c   
       1 | A   

CREATE TABLe sales2 (location int, code varchar(10))

INSERT INTO sales2 VALUEs (1,NULL),(2,'B'),(3,'c'),(1,'A')

4 rows affected

Update sales2 
SET code = src.code
FROM sales2 as src
WHERE src.location = sales2.location and sales2.code is NULL AND src.code IS NOT NULL

        ;

1 rows affected

SELECT * FROM sales2

location | code
-------: | :---
       2 | B   
       3 | c   
       1 | A   
       1 | A   

db<>fiddle here