If you have unique index on Customers.SSN in you database then you can
do it in SQLite like this:
INSERT OR REPLACE INTO Customers
(SSN, Street, HouseNo, City)
SELECT SSN, Street, HouseNo, City FROM Moved;
Pavel
On Tue, Sep 1, 2009 at 2:54 PM, Gerald Ebner<geraldo.eb...@gmail.com> wrote:
> hm, finally I'm looking for a convenient way to execute update-selects,
> very helpful in doing synchronization between different databases
>
> is there in SQLite any other way?
>
> e.g.: there is a SQL-server-proprietary UPDATE FROM syntax, e.g.:
>
> UPDATE c
> SET Street = m.Street,
> HouseNo = m.HouseNo,
> City = m.City
> FROM Customers AS c INNER JOIN Moved AS m
> ON m.SSN = c.SSN;
>
> is this syntax supported by sqlite ?
> do you know any other convenient way to execute update-selects?
>
> many thanks in advance
> Geraldo
>
>
> Igor Tandetnik wrote:
>> Gerald Ebner <geraldo.eb...@gmail.com>
>> wrote:
>>
>>> the given syntax is ANSI sql ("row-value constructors"),
>>> see also the discussion at
>>> http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx
>>>
>>> Is it likely that row-value constructors will be implemented in the
>>> (near) future ?
>>>
>>
>> I was curious as I've never heard of row-value constructors before. So
>> I've looked at SQL92
>> (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt). The
>> document does define the concept of row value constructor - but does not
>> appear to allow their use in UPDATE statement the way you show:
>>
>> <update statement: searched> ::=
>> UPDATE <table name>
>> SET <set clause list>
>> [ WHERE <search condition> ]
>>
>> <set clause list> ::=
>> <set clause> [ { <comma> <set clause> }... ]
>>
>> <set clause> ::=
>> <object column> <equals operator> <update source>
>>
>> <object column> ::= <column name>
>>
>> <update source> ::=
>> <value expression>
>> | <null specification>
>> | DEFAULT
>>
>>
>> Perhaps this is changed in more recent editions of the standard (which
>> don't seem to be publicly available). If not, your proposed syntax
>> appears to be non-standard after all, cited article notwithstanding.
>>
>> Igor Tandetnik
>>
>>
>>
>> _______________________________________________
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
