Why isn't this MySQL statement working?

Question

I have a table Seats with the columns, SeatID, Date, RouteID and Seats. The primary key is SeatsID. The value of seats is 50, and I wish to subtract whatever the user has entered into $tickettotal from 50 and then insert the new value.

The user also has to enter which RouteID and Date they wish to travel. I want these to be inserted into the Seats table along with the new value of Seats.

If another user enters the same Date and RouteID then the Seats should be updated. Otherwise a new record should be inserted with SeatID, Date, RouteID, and the reduced value for Seats.

I thought this statement should do it but I keep getting errors back such as

Can't find string terminator '"' anywhere before EOF, although when i put " at the end of the statement i just get another syntax error line 108, near ""') ON DUPLICATE KEY UPDATE Seats=Seats-'"$tickettotal" .

---

Update

I now have it inserting fine, although on a duplicate entry of both Date and RouteID it doesn't update Seats: it still just inserts a new value. The Seats - $tickettotal isn't working either.

$dbh->do("INSERT INTO $Stable(Date,RouteID) 
VALUES ('$Tdate','$Rid') ON DUPLICATE KEY UPDATE Seats=Seats-'$tickettotal'");

Update Answer

Because i didn't have a unique column, I created one using both date and RouteID added together so.

$Tdate =~ s/-//gi;
$dateandrid = $Tdate . $Rid;

The first line removes the hyphens and the second puts them together. Then using these statements get the desired effect i wanted.

$dbh->do("INSERT INTO $Stable(RouteAndDate,Date,RouteID) VALUES    ('$dateandrid','$Tdate','$Rid') ON DUPLICATE KEY UPDATE Seats=Seats");
$dbh->do("INSERT INTO $Stable(RouteAndDate,Date,RouteID) VALUES ('$dateandrid','$Tdate','$Rid') ON DUPLICATE KEY UPDATE Seats=Seats-'$tickettotal'");

Answer 1

You must avoid SQL Injection with parameter binding:

$dbh->do( qq{
     INSERT INTO $Stable(Date, RouteID) 
     VALUES (?, ?)
     ON DUPLICATE KEY UPDATE Seats=Seats-?
     },
     undef,
     $Tdate, $Rid, $tickettotal
);