Re: [sqlite] complex update

[Adam DeVita]

Thank you for the principal.

I had to rewrite a little since I only wanted to affect the rows that
were using the max entry.

Adam

On Tue, Dec 18, 2012 at 12:58 PM, Igor Tandetnik  wrote:
> On 12/18/2012 12:27 PM, Adam DeVita wrote:
>>
>> There is a table products where has a location id.  Unfortunately
>> duplicate dictionary names got added to list of locations
>>
>> products haslocationid and a bunch of other stuff
>>
>> I can easily get the max (bad) and min  (good)  location ids
>> associated with each name  (I know I should have made the name field
>> UNIQUE... mistakes were made years ago)
>>
>> how do I write an update that essentially says
>>
>> update products set locationid = good where locationid = bad  , but do
>> it for each good & bad pair ?
>
>
> update Products set locationid = (
> select min(locationid) from Locations where name =
> (select name from Locations L where L.locationid =
> Products.locationId)
> );
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] complex update

[Igor Tandetnik]

On 12/18/2012 12:27 PM, Adam DeVita wrote:

There is a table products where has a location id.  Unfortunately
duplicate dictionary names got added to list of locations

products haslocationid and a bunch of other stuff

I can easily get the max (bad) and min  (good)  location ids
associated with each name  (I know I should have made the name field
UNIQUE... mistakes were made years ago)

how do I write an update that essentially says

update products set locationid = good where locationid = bad  , but do
it for each good & bad pair ?


update Products set locationid = (
select min(locationid) from Locations where name =
(select name from Locations L where L.locationid = Products.locationId)
);

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] complex update

[Adam DeVita]

Good day,

I'm attempting to fix some bad data:

There is a table products where has a location id.  Unfortunately
duplicate dictionary names got added to list of locations

products haslocationid and a bunch of other stuff

I can easily get the max (bad) and min  (good)  location ids
associated with each name  (I know I should have made the name field
UNIQUE... mistakes were made years ago)

how do I write an update that essentially says

update products set locationid = good where locationid = bad  , but do
it for each good & bad pair ?

I can see how to do it with insert or replace, but is there a way to
do it with UPDATE?

regards,
Adam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users