Let's assume we're using this schema:
CREATE TABLE A(Id INTEGER PRIMARY KEY, Field1, Flag);
CREATE TABLE B(Id INTEGER PRIMARY KEY, Field_Temp, Price);
CREATE INDEX B2 on B(Field_Temp, Price);
CREATE INDEX A1 on A(Field1, Id);
You can extend Igor's pattern for more than 1 column:
explain query plan
update A set Flag = coalesce(
(select 'DONE' from B
where A.Field1 = B.Field_Temp and B.Price > 0),
Flag),
Id = coalesce(
(select Id from B
where A.Field1 = B.Field_Temp and B.Price > 0),
Id);
0|0|TABLE A
0|0|TABLE B WITH INDEX B2
0|0|TABLE B WITH INDEX B2
When more than a few columns are being modified it is simpler and
more efficient to use REPLACE INTO:
-- adjust the columns in the select to match the order of
-- the columns in the table to be updated
explain query plan
replace into A
select B.Id, A.Field1, 'DONE'
from A, B
where A.Field1 = B.Field_Temp
and B.Price > 0;
0|0|TABLE A
1|1|TABLE B WITH INDEX B2
--- Andre du Plessis <[EMAIL PROTECTED]> wrote:
> The Id piece works great thanks! If I want to update 2 fields on table
> A, the following syntax doesn't work as expected, as it updates all to
> records to 'DONE'.
>
> update A set Flag = 'DONE',
> Id = coalesce(
> (select Id from B
> where A.Field1 = B.Field_Temp and B.Price > 0),
> Id);
>
> -----Original Message-----
> From: Andre du Plessis
> Sent: 16 July 2007 02:30 PM
> To: Gavin McGuinness
> Subject: FW: [sqlite] Re: inner join
>
> Here you go
> It was answered by the mighty Igor, he's like THEE guru, guru's turn to
> him for help, so don't question just follow :)
>
> -----Original Message-----
> From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
> Sent: 16 July 2007 01:49 PM
> To: SQLite
> Subject: [sqlite] Re: inner join
>
> Andre du Plessis <[EMAIL PROTECTED]> wrote:
> > I would like to be able to accomplish the following but don't see any
> > support for inner joins on update queries.
> >
> > update A
> > set Id = B.Id
> > from A inner join B
> > on A.Field1 = B.Field_Temp
> > where B.Price > 0
>
> update A set Id = coalesce(
> (select Id from B
> where A.Field1 = B.Field_Temp and B.Price > 0),
> Id);
>
> Igor Tandetnik
>
> ------------------------------------------------------------------------
> -----
> To unsubscribe, send email to [EMAIL PROTECTED]
> ------------------------------------------------------------------------
> -----
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
>
____________________________________________________________________________________
Now that's room service! Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
http://farechase.yahoo.com/promo-generic-14795097
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
