prooffice
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
update person SET pers_SecTerr = Comp_SecTerr FROM person join company on Pers_CompanyId = Comp_CompanyId and COALESCE(pers_SecTerr,'') <> COALESCE (comp_SecTerr,'');
sorry James, SQLite does not support UPDATE .... FROM statement
jfmador's query should work on both databases
ASKER
Thanks
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