Shell script to merge two list and remove duplicates

Question

I have the following code:

get_list_a()
{
    $MYSQL -B -u $USER --passwword="$PW" $DB1 <<EOF
select name, value from mytable_a
EOF
}
get_list_b()
{
    $MYSQL -B -u $USER --passwword="$PW" $DB2 <<EOF
select name, value from mytable_b
EOF
}

get_list_a >$test.txt

Now I need to combine a and b first and remove all dups(key is name, the first column) and then write them to test.txt. List a and list b by itself are assumed to be distinct. If x in a and y in b exist such that x.name=y.name, then I only want to keep x. how do I do it? note: merging in SQL is not an option since they are in different db with different collation.

An example:
get_list_a prints

aaa bbb
ccc ddd

get_list_b prints

aaa fff
ggg hhh

I want the following to be written to the file:

aaa bbb
ccc ddd
ggg hhh

Answer 1

Can you word your question a little more clearly? Can you give some short example input and example output that you'd expect? It's a little unclear exactly what you're asking.

Edit: Given what you want, this should do the trick:

get_a  > inputfile
get_b >> inputfile
perl -lne '$data{$F[0]} = $F[1] unless exists $data{$F[0]} }{ for $key (keys %data) { print "$key $data{$key}\n"}' inputfile > outputfile

The }{ is because calling perl -n causes the program (given by -e) to be wrapped in an implicit while (<STDIN>) { ... } block. The } closes the while and { opens a new code bock that runs til the implicit }

Calling perl with -l causes it the input to be auto-split into @F, similar to how awk has $1, $2, etc. Then you add the key/value pair to %data unless the key is already there.

Answer 2

Would a SQL query along these lines work? (Untested)

SELECT COALESCE(x.name,y.name),COALESCE(x.value,y.value)
FROM mytable_a AS x
FULL JOIN mytable_b AS y
ON x.name = y.name;

Edit: OK, if they're in separate DBs, and the fields are space-separated as you indicate in a comment, I would probably use associative arrays in perl or awk, letting the values from x (a) overwrite the values from y (b). Something like this (still untested):

get_list_a > x.txt
get_list_b > y.txt
cat y.txt x.txt | awk '{ data[$1] = $2; } END { for (i in data) { print i, data[i]; }}'

Answer 3

Are you removing duplicates only on duplicate keys, or values too?

The command sort -u removes duplicates (letter u is for "unique"). It has options for the sort key value expressed in characeter start and end columns. It could be as simple as (assuming fixed value lengths or fixed column formatting):

get_list_a >$test.txt
get_list_b >>$test.txt
sort -u test.txt -k<startcol>,<endcol> test.txt > output.txt

Of course, I'd rather do a merge in SQL.

Answer 4

Just for "fun", here is a solution using sed and no temporary files (just variables):

x=$(get_list_a) 
y=$(get_list_b)

while read name value
do 
    y=$(echo "$y" | sed "/^$name/ d")
done << EOF
$x
EOF

echo "$x"
echo "$y"