syntax error with updating one table into another table

[Shadowing]

Using SQL im trying to get this to work in phpmyadmin but im getting syntax error and its not really telling me where.

 

the two tables are "planets" and "game".

 

"technology" and "id" are matching column names in both tables

 

laboratories is a column in the planets table

 

 UPDATEFROM planets, UPDATETO game SET game.technology= planets.technology
+ laboratories * 1 WHERE game.id = planets.id; 

 

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATEFROM planets, UPDATETO game SET game.technology= planets.technology + labo' at line 1

 

 

[mikosiko]

http://dev.mysql.com/doc/refman/5.0/en/update.html

[Pikachu2000]

Where did you get that syntax? Did you read again to make sure you didn't miss anything?

 

 

 

 

 

[Shadowing]

thanks for the responces guys.

 

when i read the error. I see it as just listing my entire code. Accept the quotes they are showing. Its saying i need quotes?

 

To me my syntax is matching what the manual says. Idk if thats reading comphrensionon my end or what. I actually really just started using SQL lol. But now i realize its faster to test stuff using SQL before im trying stuff in php.

[mikosiko]

 

To me my syntax is matching what the manual says. Idk if thats reading comphrensionon my end or what.

 

which manual? 

did you read the link that was provided?

[Shadowing]

its using this as an example in the 5.0 manual

 

UPDATE [LOW_PRIORITY] [iGNORE] table_references

    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...

    [WHERE where_condition]

 

i dont see where its talking about a 2nd table in this. Im just completly lost

 

 

EDIT: oh wait i may of figured it out

[Shadowing]

alright i got it working but the results wasnt what I wanted and i kinda thought it wouldnt work how I thought.

this only affects 1 row. I have more then one row where the game.id = planets.id.

so its just finding one that matches and uses it. need to have them use them all.

 

 

 UPDATE planets,game SET game.technology= planets.technology + 
laboratories * 1 WHERE game.id = planets.id; 

[The Little Guy]

I think something like this is what you are looking for (test on test data first or back your table up first):

 

UPDATE planets LEFT JOIN game on (game.id = planets.id) SET game.technology = (planets.technology+ laboratories * 1);

[Shadowing]

thanks little guy for  joining in

 

that gave me a result of 0 rows affected.

 

I would think since their is no limit placed it would just do all rows where the id equals the id on the other table. But then again I read in the manual you cant use limit on this so its probably why by default it only does one row.

 

I think the manual is saying i have to select first then update

 

I dont understand why i cant simply say WHERE ALL game.id = plaents.id

[The Little Guy]

You may want to try reversing the two tables like so:

 

UPDATE game LEFT JOIN planets on (game.id = planets.id) SET game.technology = (planets.technology+ laboratories * 1);

[Shadowing]

opps sorry i was miss reading the result in my test

 

writing it both ways does the same thing just tested it. The reason why I didnt think it worked before is cause once it updated the other table and then I re ran it again the result was the same so it didnt affect the row.

 

Its because i need to add this so it keeps adding it to the table "(game.technology +

 

 UPDATE planets LEFT JOIN game on (game.id = planets.id)
SET game.technology = (game.technology + planets.technology+ laboratories * 1); 

 

either way this is doing the same as

 

 UPDATE planets,game SET game.technology= game.technology + 
planets.technology + laboratories * 1 WHERE game.id = planets.id 

 

so my problem now is that the planets table has more then one id that matches a id in the game table

so every planet the user owns will add the technology up and place it into their game table

[The Little Guy]

What are you trying to accomplish by updating these in another table? There may possibly be a better way to accomplish your task.

[Shadowing]

Each player has planets that he owns in the planet table

each planet has laboratories

 

so the amount of laboratories the player has on each planet determinds how much technology the planet has

 

Every hour i have a update that trips this. So im trying to add all the technology from every planet the user owns and put them into one amount in their game table.

 

right now its just finding one match then it goes onto the next user. It only does one planet for the user.

so if the user owns 3 planets it only does one planet.

 

 

[Pikachu2000]

On a somewhat unrelated note, what's the point of multiplication by 1? The result is the same as not multiplying at all.

 

game.technology + planets.technology+ laboratories * 1  <--- HERE

[Shadowing]

lol i thought someone might mention that. Im not sure what that amount will be later on.

 

I have a game setting page that can change that setting if I choose too. I set it to 1 for right now just so if the result in the game table turned out to be 3 then i know all 3 planets updated. Even though not including it wouldnt matter

[The Little Guy]

Why don't you just calculate that on the fly?

 

It doesn't seem like it would be an intensive query. You could even make a mysql function to do it.

 

delimiter $$

create function tech_level(user_id int)
returns int
begin
-- Set the total number of planets for the current user in the planets table
set @num_planets = (select count(*) from planets where member_id = user_id);
-- Set the total number of laboratories
set @num_labs = (select count(*) from laboratories where member_id = user_id);
return @num_planets + @num_labs;
end;

delimiter ;

 

Then when you want to see a users tech, you can do this:

 

select tech_level(12345);

[Shadowing]

yah thats how I had it set up begin with. I wasnt using a function to do it though. There is only two actual screens that display the total. kinda aggervates me now on why mysql doesnt do all the planets that match. I dont understand why it just stops at one planet matching and ignores anything else that matches of the same

 

I noticed that my query time was ( Query took 0.0013 sec ) for just 2 rows

that seems kinda high maybe. So maybe not doing this on the fly is going to hit me later on being really server extensive

 

[The Little Guy]

If you optimize your queries with good indexes your queries should run really fast.

 

I run queries on tables with 500 million rows (we call them small tables) by doing left joins, and we get results back in less than a second.

[Shadowing]

guess im not sure what a index is. I should go look that up.

 

I havnt been selecting a index for any of my columns lol

 

well when i go to add a index it says a full text index exist

 

so when i created my mailing system I have a differant table for trash, inbox, and sent mail.

and the reason everyone has been telling me to do it with only one table is cause 500 million rows isnt that many rows lol. I was thinking it would be faster seperating it lol. So im worried over nothing