I think INSERT OR REPLACE...
INSERT OR REPLACE INTO Game
SELECT ug.xxx, g.xxx
FROM Game g
JOIN UpdateMag.Game ug ON ...
WHERE ...
Since you need to provide all fields to the INSERT, you need all the g.xxx
fields not being updated, including (and most importantly) the g.id field,
since that's what will generate the REPLACE.
Note that this will cause each updated record to first be deleted and then
reinserted, so if there are any foreign keys looking into the game table, this
will probably not work.
Another option is to do the select and for each record returned, generate an
independent UPDATE statement for it.
Marc
> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of FengTao DING
> Sent: Wednesday, March 14, 2012 10:13 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there any option that can enable me to do
> INSERT or UPDATE while SELECT
>
> Thanks, Igor, Nico, and anyone pay attention to this post.
> Here are the schemas:
>
> In Server.db3
> -----------------------
> Table [game]
> Fields: 37
> [id]: TEXT
> [name]: TEXT
> [category2_id]: INTEGER
> [icon]: TEXT
> [hot_level]: TEXT
> [no_update]: INTEGER
> [size]: INTEGER
> [launcher]: TEXT
> [directory]: TEXT
> [client_path]: TEXT
> [update_time]: TEXT
> [game_version]: INTEGER
> [info_version]: INTEGER
> [modify_version]: INTEGER
> [save_config]: INTEGER
> [save_type]: INTEGER
> [save_path]: TEXT
> [env_parameter]: TEXT
> [env_copy]: TEXT
> [env_resolution]: TEXT
> [env_color]: TEXT
> [env_registry]: TEXT
> [env_lib]: TEXT
> [env_virtual_cdrom]: TEXT
> [client_not_run]: TEXT
> [client_local_run]: TEXT
> [client_remote_run]: TEXT
> [cleanup]: INTEGER
> [cleanup_exclude]: TEXT
> [auto_index]: INTEGER
> [md5_verify]: INTEGER
> [file_count]: INTEGER
> [client_cleanup]: INTEGER
> [save_path_version]: INTEGER
> [env_version]: INTEGER
> [modify_game_version]: INTEGER
> [resource_version]: INTEGER
> Indexes: 1
> [game_id_unique] UNIQUE
> [id]
> Triggers: 0
> Unique constraints: 0
> Check constraints: 0
>
> In Updatemag.db3
> --------------------------
> Table [game]
> Fields: 47
> [id]: TEXT
> [name]: TEXT
> [version]: INTEGER
> [sort]: INTEGER
> [hot]: INTEGER
> [game_id]: TEXT
> [directory]: TEXT
> [download_path]: TEXT
> [launcher]: TEXT
> [origin_launcher]: TEXT
> [site]: TEXT
> [exclude]: TEXT
> [formal_only]: INTEGER
> [delete_flag]: INTEGER
> [category2_id]: TEXT
> [recommend_site]: TEXT
> [description]: TEXT
> [save_path]: TEXT
> [operate_id]: INTEGER
> [charge_id]: INTEGER
> [hot_level]: TEXT
> [other_id1]: TEXT
> [other_id2]: TEXT
> [other_id3]: TEXT
> [other_id4]: TEXT
> [other_id5]: TEXT
> [other_id6]: TEXT
> [random_file1]: TEXT
> [random_file2]: TEXT
> [random_file3]: TEXT
> [env_parameter]: TEXT
> [env_copy]: TEXT
> [env_resolution]: TEXT
> [env_color]: TEXT
> [env_registry]: TEXT
> [env_lib]: TEXT
> [update_time]: TEXT
> [pack_result]: TEXT
> [run_time]: TEXT
> [file_number]: INTEGER
> [size]: INTEGER
> [seed_id]: TEXT
> [information_version]: INTEGER DEFAULT '1'
> [save_type]: INTEGER DEFAULT '1'
> [save_path_version]: INTEGER DEFAULT '1'
> [env_version]: INTEGER DEFAULT '1'
> [resource_version]: INTEGER DEFAULT '1'
> Indexes: 1
> [game_id_unique] UNIQUE
> [id]
> Triggers: 0
> Unique constraints: 0
> Check constraints: 0
>
> Server.db3 is main db, Updatemag.db3 is attached with alias updatemag.
> Both dbs are in the same folder, of local disk.
> Here is one of the UPDATE statements I want, the statement is not
> written in correct syntax, but just describe my intent:
>
> UPDATE game LEFT JOIN updatemag.game AS idcgame ON idcgame.id=game.id
> SET
> game.name=idcgame.name,game.launcher=idcgame.laucher,game.icon=game.dir
> ectory+idcgame.launcher,
> game.info_version=idcgame.information_version,game.modify_version=game.
> modify_version+1
> WHERE game.game_version=idcgame.version AND
> game.info_version<>idcgame.information_version
>
> Then how to do that in a right way?
>
> -
>
> On Wed, Mar 14, 2012 at 9:08 PM, Igor Tandetnik <itandet...@mvps.org>
> wrote:
>
> > FengTao DING <hwk...@motorola.com> wrote:
> > > I prefer to one connection rather than WAL if it can accomplish the
> job.
> > >
> > > But I found that, INSERT can be done in one connection, but UPDATE
> > > can
> > not.
> > >
> > > Because in my UPDATE case, the new values that will be updated come
> > > from another different table, and the UPDATE's WHERE condition also
> > > involves columns from more than one tables(actually 3),
> >
> > How does this prevent you from running an UPDATE statement on the
> > first connection, while still enabling you to do this on the second?
> I
> > don't understand the nature of the problem.
> >
> > > UPDATE statement doesn't support JOIN, so I didn't find a way to do
> > > this
> > in
> > > one connection(one query).
> >
> > Where there's a will, there's a way. Show your database schema,
> > describe exactly what kind of change you want to make.
> >
> > > Currently, the only way I can work out is that, save all SELECT
> > > result in a heavy c++ array ( include values that WHERE needs ),
> > > then use the same connection to do the UPDATE, but this should
> > not
> > > be a smart way. Could anyone point out me another method?
> >
> > Not without some more details.
> > --
> > Igor Tandetnik
> >
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Regards, Fengtao
> _______________________________________________
> 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
