0

I am moving 2 rows from a table called allCooks to one row in a table called recipeChallenge (cookAAA against cookBBB in a pizza challenge)
Each match already has an ID, and this is the row I want to UPDATE

I am doing the update twice, once for cookAAA and then for cookBBB
To update the matchID with cook A...(i-ve left the php variables in)

"UPDATE `recipeChallenge` AS v1, 
        `allCooks` AS v2 
    SET v1.`cookAAAID` = v2.`cookID`, 
        v1.`cookAAAName` = v2.`cookName` 
  WHERE     v1.`matchID`='" .$matchID."' 
        AND v2.`cookID`= '" .$cookAID."'";

and then for cook B

"UPDATE `recipeChallenge` AS v1, 
        `allCooks` AS v2 
    SET v1.`cookBBBID` = v2.`cookID`, 
        v1.`cookBBBName` = v2.`cookName` 
  WHERE     v1.`matchID`='" .$matchID."' 
        AND v2.`cookID`= '" .$cookBID."'";

The table recipeChallenge is updating, but the second update is ALSO creating a new matchID and row in the recipeChallenge table. Why?

EnglishAdam
  • 1,380
  • 1
  • 19
  • 42
  • Provide your table structure. I have a feeling, that this is "whole your approach" – Alexander Feb 18 '14 at 11:00
  • @Alex, probably the WholeWhole db structure is questionable but lets say i want to do it this way, is the whole approach to THIS problem wrong? – EnglishAdam Feb 18 '14 at 11:02
  • 1
    @Alex,@Prix, Cheers, I'm looking like I have to learn what a JOIN is. – EnglishAdam Feb 18 '14 at 11:05
  • @Prix, your question uses 1 matching variable, mine has two separate variables that need to be verified separately in the mysql – EnglishAdam Feb 18 '14 at 11:12
  • @Gamemorize so? just use 2 on it, don't see what is the difficult on it, didn't u say you would go learn/read about JOIN? – Prix Feb 18 '14 at 11:16
  • @prix, JOIN is not necessary here, why are you advocating I update a whole table with JOIN when i want to update a row? – EnglishAdam Feb 18 '14 at 11:22
  • @Gamemorize I am not advocating anything that is the right way to do it. You want to update table A with/using data from table B you can easily achieve that with JOIN and you're the one who said `I have to learn what a JOIN is`. – Prix Feb 18 '14 at 11:42

1 Answers1

1

You can do as :

"UPDATE `recipeChallenge` AS v1, 
    `allCooks` AS v2 , 
    `allCooks` AS v3 
SET v1.`cookAAAID` = v2.`cookID`, 
    v1.`cookAAAName` = v2.`cookName`,
    v1.`cookBBBID` = v3.`cookID`, 
    v1.`cookBBBName` = v3.`cookName`
WHERE     v1.`matchID`='" .$matchID."' 
    AND v2.`cookID`= '" .$cookAID."'
    AND v3.`cookID`= '" .$cookBID."'";
Rida BENHAMMANE
  • 4,111
  • 1
  • 14
  • 25