Update Multiple Rows Using Select Statement

Update multiple rows using select statement

Run a select to make sure it is what you want

SELECT t1.value AS NEWVALUEFROMTABLE1,t2.value AS OLDVALUETABLE2,*
FROM Table2 t2
INNER JOIN Table1 t1 on t1.ID = t2.ID

Update

UPDATE Table2
SET Value = t1.Value
FROM Table2 t2
INNER JOIN Table1 t1 on t1.ID = t2.ID

Also, consider using BEGIN TRAN so you can roll it back if needed, but make sure you COMMIT it when you are satisfied.

How select for update works for queries with multiple rows/results?

The documentation explains what happens as follows:

FOR UPDATE

FOR UPDATE causes the rows retrieved by the SELECT statement to be
locked as though for update. This prevents them from being locked,
modified or deleted by other transactions until the current
transaction ends. That is, other transactions that attempt UPDATE,
DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE
or SELECT FOR KEY SHARE of these rows will be blocked until
the current transaction ends; conversely, SELECT FOR UPDATE will
wait for a concurrent transaction that has run any of those commands
on the same row, and will then lock and return the updated row (or no
row, if the row was deleted). Within a REPEATABLE READ or
SERIALIZABLE transaction, however, an error will be thrown if a row
to be locked has changed since the transaction started. For further
discussion see Section 13.4.

The direct answer to your question is that Postgres cannot lock all the rows "right off the bat"; it has to find them first. Remember, this is row-level locking rather than table-level locking.

The documentation includes this note:

SELECT FOR UPDATE modifies selected rows to mark them locked, and so
will result in disk writes.

I interpret this as saying that Postgres executes the SELECT query and as it finds the rows, it marks them as locked. The lock (for a given row) starts when Postgres identifies the row. It continues until the end of the transaction.

Based on this, I think it is possible for a deadlock situation to arise using SELECT FOR UPDATE.

SQL - Update multiple records in one query

Try either multi-table update syntax

UPDATE config t1 JOIN config t2
    ON t1.config_name = 'name1' AND t2.config_name = 'name2'
   SET t1.config_value = 'value',
       t2.config_value = 'value2';

Here is a SQLFiddle demo

or conditional update

UPDATE config
   SET config_value = CASE config_name 
                      WHEN 'name1' THEN 'value' 
                      WHEN 'name2' THEN 'value2' 
                      ELSE config_value
                      END
 WHERE config_name IN('name1', 'name2');

Here is a SQLFiddle demo

UPDATE multiple rows with different values in one query in MySQL

You can do it this way:

UPDATE table_users
    SET cod_user = (case when user_role = 'student' then '622057'
                         when user_role = 'assistant' then '2913659'
                         when user_role = 'admin' then '6160230'
                    end),
        date = '12082014'
    WHERE user_role in ('student', 'assistant', 'admin') AND
          cod_office = '17389551';

I don't understand your date format. Dates should be stored in the database using native date and time types.

How to update multiple rows on table with the result of a select subquery in Mysql

I don't know what your tables looks like but I'm just assuming that the product in your inner join product (given above) is your table2 with columns p.id_product and p.quantity and your product_quantity_cart has columns id_product, price_product, c.price, and c.id_shopping_cart

Then the query could be like this;

update product_quantity_cart c JOIN product p ON p.id_product = c.id_product
set c.price_product = c.price * p.quantity
WHERE c.id_shopping_cart=7

Update multiple rows using select statements

This is how such update queries are generally done in Oracle. Oracle doesn't have an UPDATE FROM option:

UPDATE table2 t2
   SET t2.value = ( SELECT t1.value FROM table1 t1
                     WHERE t1.ID = t2.ID )
 WHERE EXISTS ( SELECT 1 FROM table1 t1
                 WHERE t1.ID = t2.ID );

The WHERE EXISTS clause will make sure that only the rows with a corresponding row in table1 are updated (otherwise every row in table2 will be updated; those without corresponding rows in table1 will be updated to NULL).

Update multiple rows using WHERE IN where using one MYSQL query

The problem is that MySQL does not allow you to use the same table in subqueries in an update or delete. I think you can simplify the logic, so the subquery is not needed.

Why not just use this?

UPDATE table_1
    SET order_id = '17'
    WHERE order_id = 41 AND status = 1;

Note: If order_id is a number, use 17 not '17' -- don't mix data types.

This assumes that id is unique.

Alternatively, if you really need list of ids, you can also use a JOIN:

UPDATE table_1 t1 JOIN
       (SELECT tt1.*
        FROM table_1 tt1
        WHERE tt1.order_id = 41 AND tt1.status = 1
       ) tt1
       ON tt1.id = t1.id
    SET t1.order_id = 17;

---

Related Topics