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

[username0]

This is my general quiry

update `Antrix`.`creat ure_proto`
set `health` = (select `health` from `antrix2`.`crea ture_proto` where `entry` = (select entry from `Antrix`.`creat ure_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.creatur e_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.crea ture_proto
SET health = dbo.antrix2.cre ature_proto.hea lth
WHERE dbo.Antrix.crea ture_proto.entr y = dbo.antrix2.cre ature_proto.ent ry and dbo.antrix2.cre ature_proto.hea lth > 25 and dbo.antrix2.cre ature_proto.att acktime > 700 and dbo.antrix2.cre ature_proto.lev el > 1 and dbo.antrix2.cre ature_proto.min damage > 1 and dbo.antrix2.cre ature_proto.max damage > 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.Da tabaseOwner.Dat abaseTable

ie

Antrix.dbo.crea ture_proto
Antrix2.dbo.cre ature_proto

NOT

dbo.Antrix.crea ture_proto
dbo.Antrix2.cre ature_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_prot o
SET health = antrix2.dbo.cre ature_proto.hea lth
WHERE Antrix' at line 1

[Infide]

This is my general quiry

update `Antrix`.`creat ure_proto`
set `health` = (select `health` from `antrix2`.`crea ture_proto` where `entry` = (select entry from `Antrix`.`creat ure_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.creatur e_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.Creatur e_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.Creatur e_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.Creatur e_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`.`creat ure_proto` set `health` = (select `health` from `antrix2`.`crea ture_proto` where antrix2.creatur e_proto.level > 1 and antrix2.creatur e_proto.maxdama ge > 1 and antrix2.creatur e_proto.mindama ge > 1 and antrix2.creatur e_proto.health > 25 and antrix2.creatur e_proto.attackt ime > 600 and antrix2.creatur e_proto.`entry` = `Antrix`.`creat ure_proto`.`ent ry`);

None of the where clauses worked ... all were changed regardless of what was said in Where.