Mysql select for update multiple rows

Question

I'm trying to figure out how to combine these two queries.

Nội dung chính Show

Can you update multiple rows in MySQL?
How do I update multiple rows in SQL with update?
Can we update multiple rows in a single update statement?
How can I update multiple rows of a single column in SQL?

SELECT `o`.`Order_ID`
FROM `Orders` `o`
JOIN `CustomerDetails` `cd` ON `cd`.`Customer_ID` = `o`.`Customer_ID`
WHERE `o`.`OrderPlaceServerTime` >= '2013-06-01 00:00:00'
AND `o`.`OrderPlaceServerTime` <= '2013-06-31 23:59:59'
AND `cd`.`SalesRep_ID` = 6

This gives me a list of Order_IDs that I need to update with the SalesRep_ID = 6 from the above Query.

After I get the list of Order_IDs from the Query above I use...

UPDATE Orders SET SalesRep_ID =  '6'
WHERE  (Order_ID = 541304
OR  Order_ID = 541597
OR  Order_ID = 542318)

Doing so updates the orders with the correct SalesRep_ID.

Ultimately I'd like to combine these to make one query where I would just change the SalesRep_ID

peterm

89.5k14 gold badges143 silver badges154 bronze badges

asked Aug 7, 2013 at 15:16

0

A solution with proper UPDATE syntax with JOIN for MySql

UPDATE Orders o JOIN CustomerDetails d 
    ON d.Customer_ID = o.Customer_ID
   SET o.SalesRep_ID = 6
 WHERE o.OrderPlaceServerTime >= '2013-06-01 00:00:00'
   AND o.OrderPlaceServerTime <= '2013-06-31 23:59:59'
   AND d.SalesRep_ID = 6

Here is SQLFiddle demo

answered Aug 7, 2013 at 15:30

petermpeterm

89.5k14 gold badges143 silver badges154 bronze badges

You can do it in a single query by just simply combining them:

UPDATE Orders SET SalesRep_ID =  '6'
WHERE Order_ID IN (
   SELECT `o`.`Order_ID`
   FROM `Orders` `o`
   JOIN `CustomerDetails` `cd` ON `cd`.`Customer_ID` = `o`.`Customer_ID`
   WHERE `o`.`OrderPlaceServerTime` >= '2013-06-01 00:00:00'
      AND `o`.`OrderPlaceServerTime` <= '2013-06-31 23:59:59'
      AND `cd`.`SalesRep_ID` = 6
);

answered Aug 7, 2013 at 15:19

Filipe SilvaFilipe Silva

20.8k5 gold badges50 silver badges67 bronze badges

There is a little trick to this. You have to fool MySQL into thinking that you are working on different tables.

UPDATE Orders SET SalesRep_ID =  '6'
WHERE  (Order_ID IN (SELECT order_id FROM (SELECT `o`.`Order_ID`
FROM `Orders` `o`
JOIN `CustomerDetails` `cd` ON `cd`.`Customer_ID` = `o`.`Customer_ID`
WHERE `o`.`OrderPlaceServerTime` >= '2013-06-01 00:00:00'
AND `o`.`OrderPlaceServerTime` <= '2013-06-31 23:59:59'
AND `cd`.`SalesRep_ID` = 6) AS TEMP));

Link to SQLFiddle

answered Aug 7, 2013 at 15:19

Brian HooverBrian Hoover

7,7932 gold badges27 silver badges41 bronze badges

You have a table students with id, score1, and score2 like this, where id is the primary key:

id	score1	score2
1	10	9
2	8	3
3	10	6
4	4	8

And here is the new table of data that you want to update to:

id	score1	score2
1	5	8
2	10	8
3	8	3
4	10	7

There are a couple of ways to do it.

1. You can either write multiple UPDATE queries like this and run them all at once:

UPDATE students SET score1 = 5, score2 = 8 WHERE id = 1;
UPDATE students SET score1 = 10, score2 = 8 WHERE id = 2;
UPDATE students SET score1 = 8, score2 = 3 WHERE id = 3;
UPDATE students SET score1 = 10, score2 = 7 WHERE id = 4;

2. Or you can UPDATE with JOIN statement:

UPDATE students s
JOIN (
    SELECT 1 as id, 5 as new_score1, 8 as new_score2
    UNION ALL
    SELECT 2, 10, 8
    UNION ALL
    SELECT 3, 8, 3
    UNION ALL
    SELECT 4, 10, 7
) vals ON s.id = vals.id
SET score1 = new_score1, score2 = new_score2;

3. Or you can use INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO students 
    (id, score1, score2)
    VALUES 
        (1, 5, 8),
        (2, 10, 8),
        (3, 8, 3),
        (4, 10, 7)
    ON DUPLICATE KEY UPDATE 
        score1 = VALUES(score1),
    score2 = VALUES(score2);

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.

Download TablePlus for Mac. It’s free anyway!

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.

Can you update multiple rows in MySQL?

Use the CASE Statement Use the SELECT statement to get the updated results. We update multiple columns on multiple rows with different values using the CASE statement that goes through all conditions and outputs an item (value) when the first condition is satisfied (like the if-then-else statement).

How do I update multiple rows in SQL with update?

There are a couple of ways to do it. INSERT INTO students (id, score1, score2) VALUES (1, 5, 8), (2, 10, 8), (3, 8, 3), (4, 10, 7) ON DUPLICATE KEY UPDATE score1 = VALUES(score1), score2 = VALUES(score2);

Can we update multiple rows in a single update statement?

Column values on multiple rows can be updated in a single UPDATE statement if the condition specified in WHERE clause matches multiple rows. In this case, the SET clause will be applied to all the matched rows.

How can I update multiple rows of a single column in SQL?

First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.

programming mysql