-4

I have face a problem with my UPDATE Query in Mysql- 

mysql_query( INSERT INTO `cost`  
             SET `cat_id` = '18', `feature_id` = '77', `type_id` = '5', 
                 `cost_from` = '600', `cost_to` = '800'  
             WHERE `type_id` = 5 && `cat_id` = 18 && `feature_id` = 77");

type_id = 5 is not in my current table where i want to SET my new values.
The result is Unsuccessful.

I tried also an UPDATE query for the same 

mysql_query( Update `cost` 
             SET `cat_id` = '18', `feature_id` = '77', `type_id` = '5', 
                 `cost_from` = '600', `cost_to` = '800' 
             WHERE `type_id` = 5 && `cat_id` = 18 && `feature_id` = 77");

What should i do to insert and update values at the same time?

jogesh_pi
  • 9,762
  • 4
  • 37
  • 65
MuteX
  • 183
  • 1
  • 1
  • 13
  • 1
    How do you want to insert and update at the same time? That doesn't makes any sense! – Rizier123 Feb 05 '15 at 06:03
  • If i have no filed then it should insert as i know, and if then it update the field. – MuteX Feb 05 '15 at 06:08
  • @Rizier123, In dynamic programming i need to update a field that is not fixed so if a new filed comes then what to update other field and insert new filed? Thats why i need both the query at the same time. – MuteX Feb 05 '15 at 06:13
  • @mpranks you are using php, then why not check the dynamic file before update or insert? – jogesh_pi Feb 05 '15 at 06:15
  • @Rizier123, http://www.plus2net.com/sql_tutorial/sql_insert_set.php – MuteX Feb 05 '15 at 06:17

2 Answers2

1

Note:

  • You don't use WHERE and SET on an INSERT query.
  • Make sure you have a table named cost along with the columns cat_id, feature_id, cost_from, cost_to, type_id.
  • You're trying to update the cost table columns with the ones you are looking for. Doesn't seem right.
  • Your first and second given sample codes have the same format, you just changed the first word, INSERT and UPDATE. Doesn't work that way.

You can, I think the recommendation of all people that would see your code, create better queries using mysql_* prepared statements.

A simple example of an INSERT query would look like this:

/* PREPARE YOUR QUERY */
$stmt = $con->prepare("INSERT INTO cost (cat_id, feature_id, type_id, cost_from, cost_to) VALUES (?,?,?,?,?)");

/* BIND PARAMETER TO THE QUERY. REPLACE NECESSARY VALUE OR VARIABLE */
$stmt->bind_param('iiiss', $catid,$featureid,$typeid,$costfrom,$costto); 

$stmt->execute(); /* EXECUTE QUERY */

A simple example of an UPDATE query:

/* PREPARE YOUR QUERY */
$stmt = $con->prepare("UPDATE cost SET cat_id=?, feature_id=?, type_id=?, cost_from=?, cost_to=? WHERE id=?");

/* BIND PARAMETER TO THE QUERY. REPLACE NECESSARY VALUE OR VARIABLE */
$stmt->bind_param('iiissi', $catid,$featureid,$typeid,$costfrom,$costto,$id); 

$stmt->execute(); /* EXECUTE QUERY */
Logan Wayne
  • 6,001
  • 16
  • 31
  • 49
0

First of all, you cannot use 'set ' with ' insert into' , and if you want to insert and update queries at the same time, then your best bet is using transaction . (you have to enclose insert and update queries in a transaction) , you cannot enclose both on a single sql statement..

This post might help you SQL Update,Delete And Insert In Same Time

Community
  • 1
  • 1
Aakash
  • 675
  • 1
  • 9
  • 28