Re: [sqlite] UPDATE without a JOIN

[Jones, Matthew]

Great thanks.

Now why didn't I think of that especially as I was messing around with ROWID 
earlier?



Cheers



> update TABLE2 set z = @z

> where rowid in (

> select t2.rowid

> from TABLE1_2 t12, TABLE2 t2

> where t12.a = @a

> and t12.b = @b

> and t2.x = t12.x

> and t2.y = t12.y

>)

>

>

> Pavel

>

> On Fri, Jun 25, 2010 at 9:04 AM, Matthew Jones 
> > wrote:

> > I've seen various posts about who to get around the lack of UPDATE with

> > a JOIN but they all seem to refer to tables joined on a single column. I

> > need to do something very similar but with two-column primary key. E.g.

> >

> > sqlite> create table TABLE1 (a int, b int, primary key(a, b));

> > sqlite> create table TABLE2 (x int, y int, z int, primary key(x, y));

> > sqlite> create table TABLE1_2 (a int, b int, x int, y int, primary

> > key(a, b, x, y));

> >

> > So I have a many to many relationship between table 1 and 2. I now want

> > to update column z in TABLE2 for all entries that match a particular row

> > in TABLE1. Obviously, TABLE1_2 can be queried to give me the keys of all

> > the rows in TABLE2 that need to be updated but how do I actually do the

> > update?

> >

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

Re: [sqlite] UPDATE without a JOIN

[Pavel Ivanov]

update TABLE2 set z = @z
where rowid in (
select t2.rowid
from TABLE1_2 t12, TABLE2 t2
where t12.a = @a
and t12.b = @b
and t2.x = t12.x
and t2.y = t12.y
)


Pavel

On Fri, Jun 25, 2010 at 9:04 AM, Matthew Jones  wrote:
> I've seen various posts about who to get around the lack of UPDATE with
> a JOIN but they all seem to refer to tables joined on a single column. I
> need to do something very similar but with two-column primary key. E.g.
>
> sqlite> create table TABLE1 (a int, b int, primary key(a, b));
> sqlite> create table TABLE2 (x int, y int, z int, primary key(x, y));
> sqlite> create table TABLE1_2 (a int, b int, x int, y int, primary
> key(a, b, x, y));
>
> So I have a many to many relationship between table 1 and 2. I now want
> to update column z in TABLE2 for all entries that match a particular row
> in TABLE1. Obviously, TABLE1_2 can be queried to give me the keys of all
> the rows in TABLE2 that need to be updated but how do I actually do the
> update?
>
> Thanks
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPDATE without a JOIN

[Matthew Jones]

I've seen various posts about who to get around the lack of UPDATE with 
a JOIN but they all seem to refer to tables joined on a single column. I 
need to do something very similar but with two-column primary key. E.g.

sqlite> create table TABLE1 (a int, b int, primary key(a, b));
sqlite> create table TABLE2 (x int, y int, z int, primary key(x, y));
sqlite> create table TABLE1_2 (a int, b int, x int, y int, primary 
key(a, b, x, y));

So I have a many to many relationship between table 1 and 2. I now want 
to update column z in TABLE2 for all entries that match a particular row 
in TABLE1. Obviously, TABLE1_2 can be queried to give me the keys of all 
the rows in TABLE2 that need to be updated but how do I actually do the 
update?

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