update table based on results from a query involving a join?

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

update table based on results from a query involving a join?

developir@yahoo.com
Does anyone know how I might accomplish this in SQLite using a single update statement?

 -- MySQL
 UPDATE accomodation a, country c
 SET a.country = c.country
 WHERE a.country_id = c.country_id;

 -- Oracle
 UPDATE accomodation a
 SET a.country = (
  SELECT c.country
  FROM country c
  WHERE c.country_id = a.country_id
 );

Is it possible to update a column in a table with a value and where clause both based on the
result set of a multi-table join select statement?

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 
Reply | Threaded
Open this post in threaded view
|

Re: update table based on results from a query involving a join?

Igor Tandetnik
Joe Wilson wrote:

> Does anyone know how I might accomplish this in SQLite using a single
> update statement?
>
> -- MySQL
> UPDATE accomodation a, country c
> SET a.country = c.country
> WHERE a.country_id = c.country_id;
>
> -- Oracle
> UPDATE accomodation a
> SET a.country = (
>  SELECT c.country
>  FROM country c
>  WHERE c.country_id = a.country_id
> );

SQLite should support the second syntax. Do you have a problem with it?

Igor Tandetnik
Reply | Threaded
Open this post in threaded view
|

Re: Re: update table based on results from a query involving a join?

developir@yahoo.com
It results in a error - it stops parsing the statement at the alias 'a'. It seems simple, but I
don't believe it is possible to do this in SQLite. I've tried a dozen common syntax variants used
by other databases without success.

Here is the syntax of the SQLite UPDATE statement:

sql-statement ::= UPDATE [ OR conflict-algorithm ] [database-name .] table-name
SET assignment [, assignment]*
[WHERE expr]

assignment ::= column-name = expr

--- Igor Tandetnik <[hidden email]> wrote:

> Joe Wilson wrote:
> > Does anyone know how I might accomplish this in SQLite using a single
> > update statement?
> >
> > -- MySQL
> > UPDATE accomodation a, country c
> > SET a.country = c.country
> > WHERE a.country_id = c.country_id;
> >
> > -- Oracle
> > UPDATE accomodation a
> > SET a.country = (
> >  SELECT c.country
> >  FROM country c
> >  WHERE c.country_id = a.country_id
> > );
>
> SQLite should support the second syntax. Do you have a problem with it?
>
> Igor Tandetnik
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 
Reply | Threaded
Open this post in threaded view
|

Re: update table based on results from a query involving a join?

Igor Tandetnik
In reply to this post by developir@yahoo.com
Joe Wilson wrote:
> It results in a error - it stops parsing the statement at the alias
> 'a'. It seems simple, but I don't believe it is possible to do this
> in SQLite. I've tried a dozen common syntax variants used by other
> databases without success.

Try

 UPDATE accomodation
 SET country = (
  SELECT c.country
  FROM country c
  WHERE c.country_id = accomodation.country_id
 );

Igor Tandetnik

Reply | Threaded
Open this post in threaded view
|

Re: Re: update table based on results from a query involving a join?

developir@yahoo.com
Yes, that did the trick. Thanks.

--- Igor Tandetnik <[hidden email]> wrote:

>  UPDATE accomodation
>  SET country = (
>   SELECT c.country
>   FROM country c
>   WHERE c.country_id = accomodation.country_id
>  );
>
> Igor Tandetnik
>
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com