469,626 Members | 883 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

Help with a quiry - update is whiping my existing data when it shouldent?

This is my general quiry

update `Antrix`.`creature_proto`
set `health` = (select `health` from `antrix2`.`creature_proto` where `entry` = (select entry from `Antrix`.`creature_proto`);

I have a dB named Antrix and one named antrix2
I want to overwrite the data in colums Antrix.creature_proto.(health) with the data from antrix2.creature_proto.(health)....

this works... but hte problem is.... where entries in Antrix.entry arent in antrix2.entry.. it sets the data from Antrix.health to 0 on those entries.

Any ideas how to stop this?
Jul 11 '07 #1
10 1587
....where entries in Antrix.entry arent in antrix2.entry.. it sets the data from Antrix.health to 0 on those entries.

Yes, It Will !

Try something like :

Expand|Select|Wrap|Line Numbers
  1. UPDATE dbo.Antrix.creature_proto
  2. SET health = dbo.Antrix2.creature_proto.health
  3. WHERE dbo.Antrix.creature_proto.entry = dbo.Antrix2.creature_proto.entry
  4.  
I haven't tested it, but I think it will put you on the right track.
Jul 11 '07 #2
UPDATE dbo.Antrix.creature_proto
SET health = dbo.antrix2.creature_proto.health
WHERE dbo.Antrix.creature_proto.entry = dbo.antrix2.creature_proto.entry and dbo.antrix2.creature_proto.health > 25 and dbo.antrix2.creature_proto.attacktime > 700 and dbo.antrix2.creature_proto.level > 1 and dbo.antrix2.creature_proto.mindamage > 1 and dbo.antrix2.creature_proto.maxdamage > 1;


this is my query now.

it says you have an error in syntax near dbo ect

i cant find a problem.
Jul 12 '07 #3
Does that mean you've sorted the problem out ?
Jul 12 '07 #4
My mistake.....the format should be : DatabaseName.DatabaseOwner.DatabaseTable

ie

Antrix.dbo.creature_proto
Antrix2.dbo.creature_proto

NOT

dbo.Antrix.creature_proto
dbo.Antrix2.creature_proto
Jul 12 '07 #5
still getting this error

ERROR 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 '.creature_proto
SET health = antrix2.dbo.creature_proto.health
WHERE Antrix' at line 1
Jul 12 '07 #6
Infide
28
This is my general quiry

update `Antrix`.`creature_proto`
set `health` = (select `health` from `antrix2`.`creature_proto` where `entry` = (select entry from `Antrix`.`creature_proto`);

I have a dB named Antrix and one named antrix2
I want to overwrite the data in colums Antrix.creature_proto.(health) with the data from antrix2.creature_proto.(health)....

this works... but hte problem is.... where entries in Antrix.entry arent in antrix2.entry.. it sets the data from Antrix.health to 0 on those entries.

Any ideas how to stop this?
Expand|Select|Wrap|Line Numbers
  1. Update Creature_proto
  2. set 'health' = cp2.Health
  3. from antrix2.Creature_proto cp2
  4.    inner join antrix.creature_proto cp1
  5.         on cp1.entry = cp2.entry
  6.  
Jul 12 '07 #7
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 ''health' = cp2.Health
from antrix2.Creature_proto cp2
inner join Antrix.creatu' at line 2


still getting errors
thanks for trying
Jul 12 '07 #8
Infide
28
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 ''health' = cp2.Health
from antrix2.Creature_proto cp2
inner join Antrix.creatu' at line 2


still getting errors
thanks for trying
Expand|Select|Wrap|Line Numbers
  1. Update antrix.Creature_proto
  2. set health = cp2.Health
  3. from antrix2.Creature_proto cp2
  4.    inner join antrix.creature_proto cp1
  5.         on cp1.entry = cp2.entry
  6.  
Jul 12 '07 #9
no luck
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 ''health' = cp2.Health
from antrix2.Creature_proto cp2
inner join Antrix.creatu' at line 2
Jul 13 '07 #10
it seems that my original query works just that it dosent consider the where clause in the Select from bit..


update `Antrix`.`creature_proto` set `health` = (select `health` from `antrix2`.`creature_proto` where antrix2.creature_proto.level > 1 and antrix2.creature_proto.maxdamage > 1 and antrix2.creature_proto.mindamage > 1 and antrix2.creature_proto.health > 25 and antrix2.creature_proto.attacktime > 600 and antrix2.creature_proto.`entry` = `Antrix`.`creature_proto`.`entry`);

None of the where clauses worked ... all were changed regardless of what was said in Where.
Jul 13 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Google Mike | last post: by
21 posts views Thread by Dan | last post: by
4 posts views Thread by MikeY | last post: by
4 posts views Thread by Jm | last post: by
2 posts views Thread by Calvin KD | last post: by
2 posts views Thread by Anantha | last post: by
1 post views Thread by anjangoswami06 | last post: by
1 post views Thread by dasilva109 | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.