CODEWITHSUNDEEP
mysqljoin
cgweb87
cgweb87

Reputation: 461

JOIN UPDATE mysql, can't get it working

how do I use joins when updating in mysql? So far I have this...

$q = $dbc -> prepare("UPDATE items i JOIN accounts a SET i.shrapnel = i.shrapnel-1000, a.gender = a.? WHERE id = ? LIMIT 1");
$q -> execute(array($gender, $user['id']));

Am very new to this, what am I doing wrong?

Upvotes: 1

Views: 236

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

These work provided

  • if the id field is in both items and accounts tables
  • id is the same datatype in both tables

You can try

$q = $dbc -> prepare("UPDATE items i JOIN accounts a USING (id) SET i.shrapnel = i.shrapnel-1000, a.gender = a.? WHERE i.id = ? LIMIT 1");
$q -> execute(array($gender, $user['id']));

also

$q = $dbc -> prepare("UPDATE items i JOIN accounts a ON i.id = a.id SET i.shrapnel = i.shrapnel-1000, a.gender = a.? WHERE i.id = ? LIMIT 1");
$q -> execute(array($gender, $user['id']));

also

$q = $dbc -> prepare("UPDATE items i NATURAL JOIN accounts a SET i.shrapnel = i.shrapnel-1000, a.gender = a.? WHERE i.id = ? LIMIT 1");
$q -> execute(array($gender, $user['id']));

Upvotes: 1

daghan
daghan

Reputation: 1028

may you need a join ON something?:

$q = $dbc -> prepare("
UPDATE 
  items i JOIN accounts a ON (i.commonField = a.commonField) 
SET 
  i.shrapnel = i.shrapnel-1000 , 
  a.gender = a.? 
WHERE 
  id = ? LIMIT 1");
$q -> execute(array($gender, $user['id']));

check: http://dev.mysql.com/doc/refman/5.0/en/join.html

Upvotes: 0

Tim Withers
Tim Withers

Reputation: 12059

The id needed a table identifier. I would also use "AS".

$q = $dbc -> prepare("UPDATE items AS i JOIN accounts AS a SET i.shrapnel = i.shrapnel-1000, a.gender = a.? WHERE a.id = ? LIMIT 1");
$q -> execute(array($gender, $user['id']));

Upvotes: 0

Related Questions