Postgres Pro
Downloads
Home   >   mailing lists

Thread: Assistance with SQL

Assistance with SQL

From
Aaron Burnett
Date:

Hi,

I’m hoping someone can help me out on this one.

Two tables

table1 has 25 columns
table2 is a subset of table1 (create table2 as select id,field1,field2,field3,field4,field5,field6 from table1) with just 7 columns
There is a primary key on ID

table2 was exported to a CSV, truncated, then the “cleaned” CSV was re-imported to table2

In a nutshell I need to find the difference between the 6 columns in table2 vs table1 and update table1, again, with the ID column being the pk.


Thanks in advance for any help here.

Re: Assistance with SQL

From
hubert depesz lubaczewski
Date:
On Sun, Aug 31, 2008 at 11:31:32PM -0400, Aaron Burnett wrote:
> table1 has 25 columns
> table2 is a subset of table1 (create table2 as select
> id,field1,field2,field3,field4,field5,field6 from table1) with just 7
> columns
> There is a primary key on ID
> table2 was exported to a CSV, truncated, then the ³cleaned² CSV was
> re-imported to table2
> In a nutshell I need to find the difference between the 6 columns in table2
> vs table1 and update table1, again, with the ID column being the pk.

update table1 as t1
set
    field1 = t2.field1,
    field2 = t2.field2,
    field3 = t2.field3,
    field4 = t2.field4,
    field5 = t2.field5,
    field6 = t2.field6
from
    table2 t2
where
    t1.id = t2.id
    and (
        ( t1.field1 is distinct from t2.field1 ) OR
        ( t1.field2 is distinct from t2.field2 ) OR
        ( t1.field3 is distinct from t2.field3 ) OR
        ( t1.field4 is distinct from t2.field4 ) OR
        ( t1.field5 is distinct from t2.field5 ) OR
        ( t1.field6 is distinct from t2.field6 )
    );

should work.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Re: Assistance with SQL

From
Aaron Burnett
Date:
Thank you.

I was making it way too over-complicated.

Works perfectly


On 9/1/08 3:39 AM, "hubert depesz lubaczewski" <depesz@depesz.com> wrote:

> On Sun, Aug 31, 2008 at 11:31:32PM -0400, Aaron Burnett wrote:
>> table1 has 25 columns
>> table2 is a subset of table1 (create table2 as select
>> id,field1,field2,field3,field4,field5,field6 from table1) with just 7
>> columns
>> There is a primary key on ID
>> table2 was exported to a CSV, truncated, then the ³cleaned² CSV was
>> re-imported to table2
>> In a nutshell I need to find the difference between the 6 columns in table2
>> vs table1 and update table1, again, with the ID column being the pk.
>
> update table1 as t1
> set
>     field1 = t2.field1,
>     field2 = t2.field2,
>     field3 = t2.field3,
>     field4 = t2.field4,
>     field5 = t2.field5,
>     field6 = t2.field6
> from
>     table2 t2
> where
>     t1.id = t2.id
>     and (
>         ( t1.field1 is distinct from t2.field1 ) OR
>         ( t1.field2 is distinct from t2.field2 ) OR
>         ( t1.field3 is distinct from t2.field3 ) OR
>         ( t1.field4 is distinct from t2.field4 ) OR
>         ( t1.field5 is distinct from t2.field5 ) OR
>         ( t1.field6 is distinct from t2.field6 )
>     );
>
> should work.
>
> Best regards,
>
> depesz