Combine the update and insert query in same statement in mysql

Question

is it possible to combine the update and insert query in same statement apart from transactions and stored procedure.

For ex:At a time i want to update the records in table1 as well as insert the data in table2.

chandresh_cool · Accepted Answer · 2013-04-23 09:00:44Z

2

It is possible using

multi_query

You can fin more about it here

Something like this

 $link = mysqli_connect("server", "user", "pass", "db");

 if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
 }


$testquery .= "INSERT INTO x (`agent_name`, `job_number`, `job_value`, `points_value`) SELECT agent_name, job_number, job_value, points_value FROM jobs WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";

$testquery .= "SELECT agent_name, SUM(job_value), SUM(points_value) FROM leaderboard GROUP BY agent_name ORDER BY SUM(points_value) DESC";

mysqli_multi_query($link, $testquery) or die("MySQL Error: " . mysqli_error($link) . "<hr>\nQuery: $testquery");

edited Apr 23, 2013 at 9:00

answered Apr 23, 2013 at 8:42

chandresh_cool

11.9k3 gold badges32 silver badges46 bronze badges

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

2 Comments

soundari Over a year ago

can you tel me sample query

soundari Over a year ago

i need sample query bcoz i tried many ways.but its not working.

Joem Maranan · Accepted Answer · 2013-04-23 09:15:22Z

0

You can just type it and automatically mysqli will read it. like

@mysql_query("INSERT INTO x (`name`,`age`) VALUES ('me','21')");
@mysql_query("UPDATE y SET `status`='verified' WHERE `name`='me'");

answered Apr 23, 2013 at 9:15

Joem Maranan

1509 bronze badges

Collectives™ on Stack Overflow

Combine the update and insert query in same statement in mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related