On 10/24/2013 07:34 PM, Igor Korot wrote:
Igor,
On Thu, Oct 24, 2013 at 12:57 PM, Igor Tandetnik <i...@tandetnik.org> wrote:
On 10/24/2013 3:23 PM, Igor Korot wrote:
Will this query work:
UPDATE a SET a.field1 = (SELECT b.field1 FROM b AS myfield), a.field2 =
myfield...
or I will have to repeat subquery for a.field2?
I'm pretty sure you will have to repeat the subquery. In your statement,
myfield is an (unused) alias for table b, not for a value produced by the
subquery (which I don't know of any way to name or reuse).
Well, let me try to explain myself.
Consider following schema:
CREATE TABLE players( playerid INTEGER, name TEXT, rank INTEGER, PRIMARY
KEY playerid);
CREATE TABLE leagueplayers(playerid INTEGER, leagueid INTEGER,
original_rank INTEGER, current_rank INTEGER);
The players table is populated.
What I'm trying to do is to populate the second table with the last 2
fields: original_rank and current_rank.
So I was thinking of something like this:
UPDATE leagueplayers SET original_rank = (SELECT rank FROM players AS a),
current_rank= a WHERE players.playerid = leagueplayers.playerid;
But I guess this query will not work. ;-)
Any idea how to make the proper query?
Thank you.
You could try
REPLACE LEAGUEPLAYERS (playerid, leaqueid, original_rank, current_rank)
SELECT L.playerid, L.leagueid, P.rank, P.rank
FROM leagueplayers L INNER JOIN players P ON L.playerid = P.playerid;
That would replace the existing record with the new data.
But I think sqlite would notice that the subquery was the same in the
UPDATE statement that Igor T gave and not run it twice, so that would
probably be faster.
David
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
