Update multiple records using UNION

[forTheDogs]

Hi!  I hope this is the correct way to present this!

 

Any help would really be appreciated!

 

WAMP Server 2.0

Apache Version du serveur: 5.0.45-community-nt

Version du protocole: 10

Serveur: localhost via TCP/IP

Version du client MySQL: 5.0.45

Extensions PHP utilisées: mysqli

 

Applies ONLY to the table:

 

pedigrees

ID  RegName  SireID  Sire  DamID DAM

 

I want to update the SireID in all the records in the 'pedigrees' table that have a name in the 'Sire' field but do not have a 'SireID'

For each of these records

using the same table-pedigrees and the name in the Sire field

Find the record :  WHERE the 'RegName' is the same as the name in the 'Sire' field

Then take the 'ID' from that second record

UPDATE the 'SireID' with the 'ID' 

 

I am using phpMyAdmin and am working within the table.

I have tried using for example

 

pedigrees1.RegName instead of just RegName for all of the fields.

 

MY CODE

(SELECT RegName, ID FROM pedigrees1

WHERE SireID <1)

UNION

(SELECT Sire FROM pedigrees2

WHERE Sire != "")

UPDATE pedigrees

SET SireID = pedigrees1.ID

WHERE pedigrees1.RegName = pedigrees2.Sire

 

SQL ERROR Message

(

 

SELECT RegName, ID

FROM pedigrees1

WHERE SireID <1

)

UNION (

 

SELECT Sire

FROM pedigrees2

WHERE Sire != ""

)

UPDATE pedigrees SET SireID = pedigrees1.ID WHERE pedigrees1.RegName = pedigrees2.Sire LIMIT 0 ,

30

 

MySQL a répondu:

 

#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 'UPDATE pedigrees  SET SireID = pedigrees1.ID  WHERE pedigrees1.RegName = pedigre' at line 1

 

[fenway]

You can't mix those statements that way.... first, you want a join, not a union.  Second, you have to put these clauses into your update statement after your set clause.

[forTheDogs]

Thanks - can you tell me which type of join to use if you are working with just ONE Table.

 

Also, can anyone point me to a sample of a Join where just one table is being used?

 

Thanks!

[fenway]

Thanks - can you tell me which type of join to use if you are working with just ONE Table.

 

Also, can anyone point me to a sample of a Join where just one table is being used?

 

Thanks!

How can you join something to nothing? One table, no JOIN.