CODEWITHSUNDEEP
mysqlsqlsql-update
Haradzieniec
Haradzieniec

Reputation: 9338

complex sql update

The table MyTable contains 4 columns. 

id - phone -        salary   - zipcode

1  - 61730459987 - $56052.45 - 02456

2 - 21249620709 - NULL -      10023

3 - 21200830859 - $39089.28 - 10023

...

10000 - 64600830857 - $46063.72 - 03795**

I need to remove NULLs by replacing them with salary_estimates, based on information from the same zip-codes.

As for the example above, NULL at row #2 could be replaced with $39089.28 (we can find from the table that another person with the same zip-code 10023 earns $39089.28, so it's OK to update NULL for #2 with $39089.28). So, after we run this query, the number of empty cells could be reduced (although not up to zero).

Could anybody suggest an update query for this problem?

Upvotes: 1

Views: 403

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

UPDATE
      MyTable AS tu
  JOIN 
      ( SELECT zipcode
             , AVG(salary) AS SalaryEstimate          --- AVG, MAX, MIN
                                                      --- your choice
        FROM MyTable 
        WHERE salary IS NOT NULL
        GROUP BY zipcode
      ) AS te
    ON te.zipcode = tu.zipcode
SET 
      tu.salary = te.SalaryEstimate
WHERE 
      tu.salary IS NULL

Upvotes: 1

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

I am not sure it will work for mysql. But in MSSQL you can do like this :

UPDATE MyTable
SET salary = (select top 1 salary from MyTable where zipcode = t1.zipcode and salary is not null)
FROM MyTable t1 
WHERE t1.salary is null

You need to check equivalent join case for mysql.

Ref. Test Query :

create table #temp (id int, salary decimal(18,2), zipcode varchar(10))
insert into #temp values (1,56052.45,02456)
insert into #temp values (2,NULL,1023)
insert into #temp values (3,39089.28,1023)
insert into #temp values (4,46063.72,03795)
insert into #temp values (5,NULL,02456)

UPDATE #temp
SET salary = (select top 1 salary from #temp where zipcode = t1.zipcode and salary is not null)
FROM #temp t1 
WHERE t1.salary is null

select * from #temp
drop table #temp

Upvotes: 1

Jinesh Jain
Jinesh Jain

Reputation: 1222

You can do like this: if salary is null it will replace with salary of that zipcode.

isnull(salary,(Select top 1 salary  from  MyTable  where zipcode=10023 ))

Upvotes: 0

Related Questions