CODEWITHSUNDEEP
mysqlsql
Wanderer
Wanderer

Reputation: 592

MySQL query to fill in a table with other data from that table

I have a table with a set of data like

record_date  |  id   | category | model    | name | alternate_name
-------------------------------------------------------------------
9/1/2012     |  N42  |   X      |  ABC     | blah |  blah
10/1/2011    |  N42  |   Y      |  No Code | #N/A |  #N/A
6/1/2012     |  N42  |   X      |  DEF     | xxxx |  yyyy
7/1/2011     |  N42  |   Z      |  No Data | #N/A |  #N/A

Since the dataset is not complete I want to fill the missing data (model, name, alternate_name) with data from the most recent record containing data matching on the id field.

i.e. I want it to look something like this

record_date  |  id   | category | model    | name | alternate_name
-------------------------------------------------------------------
9/1/2012     |  N42  |   X      |  ABC     | blah |  blah
10/1/2011    |  N42  |   Y      |  ABC     | blah |  blah
6/1/2012     |  N42  |   X      |  DEF     | xxxx |  yyyy
7/1/2011     |  N42  |   Z      |  ABC     | blah |  blah

Upvotes: 0

Views: 204

Answers (3)

Wanderer
Wanderer

Reputation: 592

Thank you for the suggestions however neither seemed to work quite right. I ended up doing this in two steps.

use myDB;
drop table if exists tmp_myTable;
create temporary table tmp_myTable like myTable;
insert into tmp_myTable select * from myTable as t1
  where record_date in (select max(record_Date)
    from myTable where id=t1.id 
    and (model!="No Code" or model="No Data")
    and name!="#N/A")
  and (category="X"
    or category="Y"
    or category="Z")
  and (model!="No Code" or model="No Data")
  and name!="#N/A";

update myTable as t1 join tmp_myTable as t2 on t1.id=t2.id
  set t1.model=t2.model, 
      t1.name=t2.name,
      t1.alternate_name=t2.alternate_name
  where (t1.category="X"
    or t1.category="Y"
    or t1.category="Z")
  and (t1.model="No Code" or t1.model="No Data")
  and t1.name="#N/A";

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Here is one method that uses three correlated subqueries:

update "table" t
    set model = (select model from "table" t2 where t2.id = t.id and t2.record_date < t.record_date and model <> 'No Code' order by t2.record_date limit 1),
        name = (select name from "table" t2 where t2.id = t.id and t2.record_date < t.record_date and name <> '#N/A' order by t2.record_date limit 1),
        alternate_name  = (select alternate_name from "table" t2 where t2.id = t.id and t2.record_date < t.record_date and alternate_name <> '#N/A' order by t2.record_date limit 1)      
    where model = 'No Code' and name = '#N/A' and alternate_name = '#N/A';

I would recommend that you have a unique id on each row.

Upvotes: 1

Praveen Lobo
Praveen Lobo

Reputation: 7187

Assuming you mean NULL by no data you can use these query to update one column. You can replace the IS NULL with something like = "No Data"

UPDATE tableName A JOIN tableName B ON A.id = B.id
SET A.dummyID = B.dummyID
WHERE A.dummyID IS NULL AND B.dummyID IS NOT NULL;

see example

You should run similar queries for other columns. You may combine such queries into one but it will complicate the query and make it less readable.

Say you have another column called anotherColumn and the "N/A" means no data, you can use following query - 

UPDATE tableName A JOIN tableName B ON A.id = B.id JOIN tableName C ON A.id = C.id
SET A.dummyID = B.dummyID, C.anotherColumn = B.anotherColumn
WHERE A.dummyID IS NULL AND B.dummyID IS NOT NULL AND
C.anotherColumn = "N/A" AND B.anotherColumn <> "N/A";

See example

Upvotes: 0

Related Questions