Re: [sqlite] UPDATE - to simplify the code...

2008-12-04 Thread Igor Tandetnik 
"Ernany" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
>> Is it possible to simplify the code? I have ten fields to UPDATE.
>> (STATUS, Responsavel, Country,..)
>>
>> UPDATE Bens SET *STATUS *=
>>  CASE (SELECT COUNT(BENSBK.STATUS) FROM BENSBK WHERE BENSBK.BEM =
>> Bens.BEM)
>>  WHEN 0 THEN Bens.STATUS
>>  ELSE (SELECT BENSBK.STATUS FROM BENSBK WHERE BENSBK.BEM = Bens.BEM)
>>  END
> [similar statements snipped]
>
> Try this:
>
> update Bens
> set STATUS=coalesce(
>   (SELECT BENSBK.STATUS FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
>   STATUS),
> set Responsavel=coalesce(
>   (SELECT BENSBK.Responsavel FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
>   Responsavel),
> set Country=coalesce(
>   (SELECT BENSBK.Country FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
>   Country);
>
> *
> ERROR: near "set": syntax error*

Keep the first instance of "set", remove the other two. As in

update Bens set STATUS=...,
 Responsavel=...,
 Country=...;

> *another question: can I update tables of two different files?*

See ATTACH statement: http://sqlite.org/lang_attach.html

Igor Tandetnik



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

Re: [sqlite] UPDATE - to simplify the code...

2008-12-04 Thread Ernany 
Hello Igor,

> Is it possible to simplify the code? I have ten fields to UPDATE.
> (STATUS, Responsavel, Country,..)
>
> UPDATE Bens SET *STATUS *=
>  CASE (SELECT COUNT(BENSBK.STATUS) FROM BENSBK WHERE BENSBK.BEM =
> Bens.BEM)
>  WHEN 0 THEN Bens.STATUS
>  ELSE (SELECT BENSBK.STATUS FROM BENSBK WHERE BENSBK.BEM = Bens.BEM)
>  END
[similar statements snipped]

Try this:

update Bens
 set STATUS=coalesce(
   (SELECT BENSBK.STATUS FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
   STATUS),
 set Responsavel=coalesce(
   (SELECT BENSBK.Responsavel FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
   Responsavel),
 set Country=coalesce(
   (SELECT BENSBK.Country FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
   Country);

Igor Tandetnik
***
*
ERROR: near "set": syntax error*

Someone help me?

***
*another question: can I update tables of two different files?*

UPDATE Bens SET *STATUS * ?


*Patrim.db3*
TABLE [Bens] (
  [Situacao] NVARCHAR(1),
  [Uso] NVARCHAR(1),
  [Country] NVARCHAR(1),
  [Bem] NUMERIC(9) NOT NULL ON CONFLICT ROLLBACK PRIMARY KEY ON CONFLICT
ROLLBACK,
  [Status] NVARCHAR(1),
  [Responsavel] NVARCHAR(10));

*PatrimBK.db3*
TABLE [Bens] (
  [Situacao] NVARCHAR(1),
  [Uso] NVARCHAR(1),
  [Country] NVARCHAR(1),
  [Bem] NUMERIC(9) NOT NULL ON CONFLICT ROLLBACK PRIMARY KEY ON CONFLICT
ROLLBACK,
  [Status] NVARCHAR(1),
  [Responsavel] NVARCHAR(10));



Thanks a lot

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

Re: [sqlite] UPDATE - to simplify the code...

2008-11-13 Thread Igor Tandetnik 
"Ernany" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> Is it possible to simplify the code? I have ten fields to UPDATE.
> (STATUS, Responsavel, Country,..)
>
> UPDATE Bens SET *STATUS *=
>  CASE (SELECT COUNT(BENSBK.STATUS) FROM BENSBK WHERE BENSBK.BEM =
> Bens.BEM)
>  WHEN 0 THEN Bens.STATUS
>  ELSE (SELECT BENSBK.STATUS FROM BENSBK WHERE BENSBK.BEM = Bens.BEM)
>  END
[similar statements snipped]

Try this:

update Bens
  set STATUS=coalesce(
(SELECT BENSBK.STATUS FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
STATUS),
  set Responsavel=coalesce(
(SELECT BENSBK.Responsavel FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
Responsavel),
  set Country=coalesce(
(SELECT BENSBK.Country FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
Country);

Igor Tandetnik 



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

[sqlite] UPDATE - to simplify the code...

2008-11-13 Thread Ernany 
Hello ,

Is it possible to simplify the code? I have ten fields to UPDATE. (STATUS,
Responsavel, Country,..)

Thanks,

Ernany

**
UPDATE Bens SET *STATUS *=
  CASE (SELECT COUNT(BENSBK.STATUS) FROM BENSBK WHERE BENSBK.BEM = Bens.BEM)

  WHEN 0 THEN Bens.STATUS
  ELSE (SELECT BENSBK.STATUS FROM BENSBK WHERE BENSBK.BEM = Bens.BEM)
  END

UPDATE Bens SET *Responsavel *=
  CASE (SELECT COUNT(BENSBK.Responsavel) FROM BENSBK WHERE BENSBK.BEM =
Bens.BEM)
  WHEN 0 THEN Bens.Responsavel
  ELSE (SELECT BENSBK.Responsavel FROM BENSBK WHERE BENSBK.BEM = Bens.BEM)
  END

UPDATE Bens SET *Country *=
  CASE (SELECT COUNT(BENSBK.Country ) FROM BENSBK WHERE BENSBK.BEM =
Bens.BEM)
  WHEN 0 THEN Bens.Country
  ELSE (SELECT BENSBK.Country FROM BENSBK WHERE BENSBK.BEM = Bens.BEM)
  END


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