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
