[SOLVED] UPDATE multiple tables with rel table where relationship might not exist

[socratesone]

I have three tables (not the actual table names):

 

Voters:

VoterId | numCandidatesVotedFor

 

Candidates:

CandidateId | numVotes

 

VoterCandidateRel

FKVoterId | FKCandidateId | numVotes

 

The Voters table has the total number of Candidates that this voter has voted for

The Candidates table has the total number of times this Candidate has been voted for

The VoterCandidateRel table has the total number of times that a particular voter has voted for a particular Candidate

 

What I would LIKE to be able to do (In MySQL v. 4.0.23) is to update all three tables in one fell swoop with on querie

 

I can do this if the VoterCandidateRel table already has a record of an existing purchase with this query:

UPDATE Voters, Candidates, VoterCandidateRel SET Voters.numCandidatesVotedFor=Voters.numCandidatesVotedFor+1, 
   Candidates.numVotes=Candidates.numVotes+1,
   VoterCandidateRel.numVotes=VoterCandidateRel.numVotes+1
   WHERE VoterCandidateRel.FKVoterId = Voters.VoterId AND 
             VoterCandidateRel.FKCandidateId = Candidates.CandidateId AND
             Voters.VoterId=12345 AND Candidates.CandidateId=12345;

However, If the relationship in the VoterCandidateRel doesn't exist, I get 0 rows effected.

 

What I would like to be able to do is to UPDATE the VoterCandidateRel table if the relationship exists, and INSERT a new relationship if it doesn't yet exits, but I would like to be able to do it with a single query, if at all possible.

 

I realize I can use PHP to check if the relationship exists, then do an UPDATE or INSERT after the fact, but that would mean that I would have to make three different queries:

1) To find out if the relationship exists

2) To update the Voters and Candidates Table, and

3) To conditionally insert or update the VoterCandidateRel table.

 

I was wondering if there was any MySQL magic that I can do to do this all in one query, to avoid all the hits to the database.

 

 

 

 

[socratesone]

The easiest way to solve this was to ensure that relationship existed, so I add an addition to the rel table befor I even get here.