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

[username0]

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?

[SkinHead]

....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 :

UPDATE dbo.Antrix.creature_proto
SET health = dbo.Antrix2.creature_proto.health
WHERE dbo.Antrix.creature_proto.entry = dbo.Antrix2.creature_proto.entry
 

I haven't tested it, but I think it will put you on the right track.

[username0]

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.

[SkinHead]

Does that mean you've sorted the problem out ?

[SkinHead]

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

[username0]

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

[Infide]

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?

Update Creature_proto
set 'health' = cp2.Health
from antrix2.Creature_proto cp2
   inner join antrix.creature_proto cp1
        on cp1.entry = cp2.entry
 

[username0]

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

[Infide]

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

Update antrix.Creature_proto
set health = cp2.Health
from antrix2.Creature_proto cp2
   inner join antrix.creature_proto cp1
        on cp1.entry = cp2.entry
 

[username0]

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

[username0]

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.