Update/Join Howto?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Update/Join Howto?

Mitchell Vincent
Hi all!

PostgreSQL has me spoiled and my mind corrupted. I need a little help
doing in SQLite what I can do in PG..


UPDATE customer_detail SET customer_id = (SELECT customer_id FROM
customers WHERE customers.customer_name = customer_detail.customer_name);

I need to update the ID the customer to re-sync it with the ID in
another table..

Thanks!!

--
- Mitchell Vincent
- kBilling - Invoices Made Easy!
- http://www.k-billing.com
Reply | Threaded
Open this post in threaded view
|

Re: Update/Join Howto?

D. Richard Hipp
On Thu, 2005-06-23 at 14:48 -0400, Mitchell Vincent wrote:

> UPDATE customer_detail SET customer_id = (SELECT customer_id FROM
> customers WHERE customers.customer_name = customer_detail.customer_name);
>

This should work. Are you having problems?  You'll
need version 3.1.0 or later.
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Update/Join Howto?

Mitchell Vincent
D. Richard Hipp wrote:

> On Thu, 2005-06-23 at 14:48 -0400, Mitchell Vincent wrote:
>
>
>>UPDATE customer_detail SET customer_id = (SELECT customer_id FROM
>>customers WHERE customers.customer_name = customer_detail.customer_name);
>>
>
>
> This should work. Are you having problems?  You'll
> need version 3.1.0 or later.

Shoot! I meant to say that I need it to work with 2.8.16 (which was the
entire issue!)..

*crosses fingers* any way to make that happen?

Thanks!

--
- Mitchell Vincent
- kBilling - Invoices Made Easy!
- http://www.k-billing.com
Reply | Threaded
Open this post in threaded view
|

Re: Update/Join Howto?

D. Richard Hipp
On Thu, 2005-06-23 at 15:32 -0400, Mitchell Vincent wrote:
> Shoot! I meant to say that I need it to work with 2.8.16 (which was the
> entire issue!)..
>
> *crosses fingers* any way to make that happen?
>

Not really.  Version 2 is in maintenance - meaning
bugfixes only.  All new features go into version 3.
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Update/Join Howto?

Mitchell Vincent
D. Richard Hipp wrote:

> On Thu, 2005-06-23 at 15:32 -0400, Mitchell Vincent wrote:
>
>>Shoot! I meant to say that I need it to work with 2.8.16 (which was the
>>entire issue!)..
>>
>>*crosses fingers* any way to make that happen?
>>
>
> Not really.  Version 2 is in maintenance - meaning
> bugfixes only.  All new features go into version 3.

Well, I meant any way to accomplish what I'd like to do in version 2
(doesn't matter how)..

--
- Mitchell Vincent
- kBilling - Invoices Made Easy!
- http://www.k-billing.com
Reply | Threaded
Open this post in threaded view
|

Re: Update/Join Howto?

Dennis Cote
Mitchell Vincent wrote:

> D. Richard Hipp wrote:
>
>> On Thu, 2005-06-23 at 15:32 -0400, Mitchell Vincent wrote:
>>
>>> Shoot! I meant to say that I need it to work with 2.8.16 (which was
>>> the entire issue!)..
>>>
>>> *crosses fingers* any way to make that happen?
>>>
>>
>> Not really.  Version 2 is in maintenance - meaning
>> bugfixes only.  All new features go into version 3.
>
>
> Well, I meant any way to accomplish what I'd like to do in version 2
> (doesn't matter how)..
>
Mitchell,

You can accomplish the same thing with something like the SQL below. I
have abbreviated your field names, but you should get the idea.

    --use a transaction to ensure it all gets done (or nothing is changed)
    begin transaction

    --create a temporary detail table with the same columns as your
    permanent detail table
    create temp table t_det(c_name, c_id, c_other);

    --copy the data into the temp table from either the customer table
    -- joined by name, or the existing detail table
    insert into t_det select cd.c_name, coalesce(c.id, cd.c_id), cd.c_other
        from c_det as cd left join cust as c using(c_name);

    --delete all the records from the permanent detail table
    delete from c_det;

    --copy the temp table back into the permanent table
    insert into c_det select * from t_det;

    --delete the temp table
    drop table t_det;

    commit transaction

HTH
Dennis Cote