• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

update in sqlite

I need an sql which works on MS-SQL and sqlite and does this::

update person SET pers_SecTerr = Comp_SecTerr FROM person join company on Pers_CompanyId = Comp_CompanyId WHERE COALESCE(pers_SecTerr,'') <> COALESCE (comp_SecTerr,'');

That works on MSSQL but not on sqlite.
Help?
0
prooffice
Asked:
prooffice
  • 3
  • 2
1 Solution
 
jfmadorCommented:
you cannot use UPDATE ... FROM using SQL Lite

may be give a try to this

update person SET pers_SecTerr = (SELECT Comp_SecTerr FROM company WHERE persone.Pers_CompanyId = company.Comp_CompanyId and COALESCE(pers_SecTerr,'') <> COALESCE (comp_SecTerr,''))

http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSqlUpdateWithFrom
0
 
jfmadorCommented:
Sorry I mistyped one table and forgot to prefix your column...

update person SET pers_SecTerr = (SELECT Comp_SecTerr FROM company WHERE person.Pers_CompanyId = company.Comp_CompanyId and COALESCE(person.pers_SecTerr,'') <> COALESCE (company.comp_SecTerr,''))
0
 
jamesguCommented:
update person SET pers_SecTerr = Comp_SecTerr FROM person join company on Pers_CompanyId = Comp_CompanyId and COALESCE(pers_SecTerr,'') <> COALESCE (comp_SecTerr,'');

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
jfmadorCommented:
sorry James, SQLite does not support UPDATE .... FROM statement
0
 
jamesguCommented:
jfmador's query should work on both databases

0
 
proofficeAuthor Commented:
Thanks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now