1

This would be similar to using a spreadsheet application. I have a table t1:

date       | time     | windspd | winddir
-----------+----------+---------+--------
2014-02-16 | 00:00:00 |       0 |    NULL
2014-02-16 | 00:00:10 |       0 |    NULL
2014-02-16 | 00:00:20 |       0 |    NULL
2014-02-16 | 00:00:30 |       0 |    NULL
2014-02-16 | 00:00:40 |       0 |    NULL
2014-02-16 | 00:00:50 |       0 |    NULL
2014-02-16 | 00:01:00 |       0 |    NULL
2014-02-16 | 00:01:10 |       0 |    NULL
2014-02-16 | 00:01:20 |       0 |    NULL
2014-02-16 | 00:01:30 |       0 |    NULL

And a table t2:

date       | time     | windspd | winddir
-----------+----------+---------+--------
2014-02-16 | 00:00:00 |    NULL |       0
2014-02-16 | 00:00:10 |    NULL |       0
2014-02-16 | 00:00:20 |    NULL |       0
2014-02-16 | 00:00:30 |    NULL |       0
2014-02-16 | 00:00:40 |    NULL |       0
2014-02-16 | 00:00:50 |    NULL |       0
2014-02-16 | 00:01:00 |    NULL |       0
2014-02-16 | 00:01:10 |    NULL |       0
2014-02-16 | 00:01:20 |    NULL |       0
2014-02-16 | 00:01:30 |    NULL |       0

I want to merge these tables so each row has a non-null value for windspd and winddir. The merged table would look like the following:

date       | time     | windspd | winddir
-----------+----------+---------+--------
2014-02-16 | 00:00:00 |       0 |       0
2014-02-16 | 00:00:10 |       0 |       0
2014-02-16 | 00:00:20 |       0 |       0
2014-02-16 | 00:00:30 |       0 |       0
2014-02-16 | 00:00:40 |       0 |       0
2014-02-16 | 00:00:50 |       0 |       0
2014-02-16 | 00:01:00 |       0 |       0
2014-02-16 | 00:01:10 |       0 |       0
2014-02-16 | 00:01:20 |       0 |       0
2014-02-16 | 00:01:30 |       0 |       0

The date and time columns are identical on both tables, so I could insert the column t2.winddir to t1, if there's a way to do that.

I know that I could use a join, but I tested on a smaller size: it takes about 25 to 30-ish seconds per row testing for date and time being equal.

Is there any faster way to do this?

0

1 Answer 1

1

If date & time are identical and work as a unique identifier in both tables:

SELECT 
    t1.date,
    t1.time,
    COALESCE(t1.windspeed,t2.windspeed) as windspeed,
    COALESCE(t1.winddir,t2.winddir) as winder
FROM t1 INNER JOIN t2 ON t1.date = t2.date AND t1.time = t2.time

This is a query for create table:

CREATE TABLE tblName AS

SELECT 
      t1.date,
      t1.time,
      COALESCE(t1.windspeed,t2.windspeed) as windspeed,
      COALESCE(t1.winddir,t2.winddir) as winder
FROM t1 INNER JOIN t2 ON t1.date = t2.date AND t1.time = t2.time

To work properly (fast), create combined indexes on both tables on (date, time).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.