Re: [sqlite] updating records in table A from joined recordsintableB

[Robert Poor]

On Sat, Apr 2, 2011 at 11:10, Igor Tandetnik  wrote:
> If possible, set up your table so that INSERT OR REPLACE works for it. E.g. 
> declare "key" field unique, or primary key.

Yah, I've used INSERT OR REPLACE -- it's a nice extension that works
like a champ.

I'm writing a cross-db library function that does essentially INSERT
OR REPLACE and INSERT OR IGNORE and INSERT OR FAIL.  Ironically, I
can't use those SQLite extensions because the library functions need
to work on user-supplied tables where I don't control the indices.

I've thought about setting up the indeces (and tearing them down)
simply so I could use INSERT OR REPLACE, but that would quickly eat up
any time advantage when given a large table with compound keys.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] updating records in table A from joined recordsintableB

[Igor Tandetnik]

Robert Poor  wrote:
> On Fri, Apr 1, 2011 at 21:36, Igor Tandetnik  wrote:
>> update table_a set avalue = (
>> select bvalue from table_b where akey=bkey
>> union all
>> select avalue);
> 
> That also works.  But at the risk of "moving the finish line during
> the race", I should point out that the original question was how to
> update entire records, not individual fields.

If possible, set up your table so that INSERT OR REPLACE works for it. E.g. 
declare "key" field unique, or primary key.

> DROP TABLE IF EXISTS `table_a`; CREATE TABLE `table_a` (`key` int(11),
> `value` float, `str` varchar(255));
> DROP TABLE IF EXISTS `table_b`; CREATE TABLE `table_b` (`key` int(11),
> `value` float, `str` varchar(255));
> ==
> I'm looking for a construct that joins on the 'key' field, but updates
> both the 'value' and 'str' fields, with the result:
> 
> select * from table_a;
> 1 | 101.0 | 'apr'
> 2 | 102.0 | 'may'
> 3 |3.0 | 'wed'

Assuming "key" is declared unique:

insert or replace into table_a(key, value, str)
select key, value, str from table_b;

If it's possible to have records in table_b without a match in table_a, and 
assuming you don't actually want to insert such records, then

insert or replace into table_a(key, value, str)
select b.key, b.value, b.str
from table_b b join table_a a on (a.key = b.key);

> (FWIW, both PostgreSQL and MySQL have extensions to UPDATE that make
> this possible w/o subqueries.

Yes, it is unfortunate that SQLite doesn't implement such an extension.
-- 
Igor Tandetnik

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