It will work but remember that you have to mention in INSERT all
fields that you want to have non-default and non-null values. So if
you want just to update a couple of fields and leave as is all others
then you'll need to do something like this:
INSERT OR REPLACE INTO Customers
(SSN, Street, HouseNo, City, ... <all other fields>)
SELECT c.SSN, m.Street, m.HouseNo, m.City, c... <all other fields from c>
FROM Moved m, Customers c
WHERE c.SSN = m.SSN;
Pavel
On Tue, Sep 1, 2009 at 3:04 PM, Gerald Ebner<geraldo.eb...@gmail.com> wrote:
> thanks for the quick response!
>
> we typically have 3 unique keys on every table:
> - on the ID used for foreign keys
> - on the global unique ID used for synchronization
> - and on 2..3 fields that must be unique according to the problem domain
> (e.g. name + surname + birth date)
>
> will INSERT OR REPLACE also work in our situation?
>
> take care
> Geraldo
>
>
> Pavel Ivanov wrote:
>> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
