mysql - Overwrite default values from source table using union -

in table, have few specific keys , few generic keys. specific data can got combining specific key column values on top of generic key column values.

specific key  | generic key   | col1        | col2          |  null          | generic key 1 | defaultval1 | default val2  | specific key1 | generic key 1 | null        | specific val2 | 

in case specific record should need like,

specific key1 | generic key 1 | defaultval1 | specific val2| 

i trying achieve using union statement, but, overwriting null value specific key1 on top of generic defaultval1.

hence columns overwritten on top of generic records when not null. if columns of specific record null, want retain default value.

edit:

i tried provide info simple possible, looks attracts more downvotes. here trying explain schema bit further:

i have 2 tables namely ids_link, core_params

ids_link table contents:

unique key : specificid

| globalid | specificid | type |

| gid1 | sid1 | type1 |

| gid1 | sid2 | type2 |

| gid2 | sid3 | type1 |

| gid2 | sid4 | type2 |

| gid3 | sid5 | type1 |

core_params table : uniquekey : id

| id | coreparam1 | coreparam2 |

| gid1 | defaultval1 | defaultval2 |

| sid1 | null | sid1val2 |

| sid2 | sid2val1 | null |

| sid3 | sid3val1 | null |

in short, more 1 specific id share global id. global id have default values corresponding specific ids. specific id have specific content needs overwritten on top of corresponding global id values , returned. example) if want return specific records type 1, output be,

| specificid | globalid | type | coreparam1 | coreparam2 |

| sid1 | gid1 | type1 | defaultval1 | sid1val2 |

| sid3 | gid2 | type1 | sid3val1 | null |

if have noticed here, sid1, in core_params table, there no value coreparam1. backfilled using default value gid1 record. sid3, there no default record backfill coreparam2. hence coreparams2 field stays null. trying write sql query achieve this. query tried :

select specificid,globalid,type,coreparam1,coreparam2 ids_link left join core_params on ids_link.globalid=coreparams.id type='type1' union select specificid,globalid,type,coreparam1,coreparam2 ids_link left join core_params on ids_link.specificid=coreparams.id type='type1' limit 10; 

but in this, if there null values records in core_params table specifickeys sid1, coreparam1 column null, want backfilled of global value (defaultval1). kindly let me know if need more info.

maybe re-create scenario.

-- create test table

create table [dbo].[test](     [col1] [nvarchar](40) null,     [col2] [nvarchar](40) null,     [col3] [nvarchar](40) null,     [col4] [nvarchar](40) null, ) 

--insert test rows

insert test values (null, 'generic key 1', 'dfaultval1', 'defaultval2') insert test values ('specific key 1', 'generic key 1', null, 'specific val 2') 

you said u use specific key in combination generic key, if true not reach defaultval1 on col1, maybe need elaborate better. try work min/max or criteria like , keep selecting subsets till filter way wanted.

select t.col1, t.col2, t.col3, t.col4  test t,       (select max(col1) good1, max(col2) good2 test) goodkey       t.col1 = goodkey.good1 , t.col2 = goodkey.good2 

hope helps.