INSERT ... ON DUPLICATE KEY UPDATE (PHP: PDO)

Question

I am having trouble understanding this ON DUPLICATE KEY UPDATE sql syntax. They all use very small examples.

Do I need to go:

INSERT INTO skaters (id,name,team,position,games_played,goals,assists,points,plus_minus,penalty_minutes,power_play_goals,power_play_points,shorthand_goals,shorthand_points,winning_goals,overtime_goals,shots,shot_percentage,time_on_ice_pg,faceoff_win_percentage) VALUES (:id,:name,:team,:position,:games_played,:goals,:assists,:points,:plus_minus,:penalty_minutes,:power_play_goals,:power_play_points,:shorthand_goals,:shorthand_points,:winning_goals,:overtime_goals,:shots,:shot_percentage,:time_on_ice_pg,:faceoff_win_percentage)
ON DUPLICATE KEY UPDATE name=values(name),team=values(team),position=values(position),games_played=values(games_played),goals=values(goals),assists=values(assists),points=values(points),plus_minus=values(plus_minus),penalty_minutes=values(penalty_minutes),power_play_goals=values(power_play_goals),power_play_points=values(power_play_points),shorthand_goals=values(shorthand_goals),shorthand_points=values(shorthand_points),winning_goals=values(winning_goals),overtime_goals=values(overtime_goals),shots=values(shots),shot_percentage=values(shot_percentage),time_on_ice_pg=values(time_on_ice_pg),faceoff_win_percentage=values(faceoff_win_percentage);

I was wondering if there was a shortcut for update all the previouse values mentioned. Or just a more concise way or writing instead of a huge list of title=values(title). Can I (name,team,position,...)=values(name,team,position,...) for example?

There's always REPLACE INTO if you're going to stomp everything anyway. — tadman
– tadman, Commented Mar 13, 2015 at 17:30
See here as well - not a duplicate question, but related: stackoverflow.com/questions/11235501/… — Peter Bowers
– Peter Bowers, Commented Mar 13, 2015 at 17:47
Not ideal, but I think this is my best solution. Will be less efficient, but I am more worried about the ridiculously obtuse SQL statement. — Jonathon
– Jonathon, Commented Mar 13, 2015 at 17:50
What are the UNIQUE/PRIMARY keys? Id id AUTO_INCREMENT? (The code looks 'wrong'.) — Rick James
– Rick James, Commented Mar 15, 2015 at 20:31
id is unique. IT is based on pre-existing ids so is not auto_increment. Updated code to exactly what I have used and tested with (it does indeed work). — Jonathon
– Jonathon, Commented Mar 15, 2015 at 20:49

Peter Bowers · Accepted Answer · 2015-03-13 17:43:35Z

2

As far as I know you have to specify each one individually.

You might save yourself some hassle/typos by building it from an array:

$flds = array('name', 'team', 'position', ...)
$sql = 'INSERT INTO table (id';
foreach ($flds as $f)
    $sql .= ','.$f;
$sql .= ') VALUES (:id';
foreach ($flds as $f)
    $sql .= ',:'.$f;
$sql .= ') ON DUPLICATE KEY UPDATE ';
$sep = '';.
foreach ($flds as $f) {
    $sql .= "$sep$f=values($f)";
    $sep = ', ';
}

answered Mar 13, 2015 at 17:43

Peter Bowers

3,1031 gold badge13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

INSERT ... ON DUPLICATE KEY UPDATE (PHP: PDO)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related