CODEWITHSUNDEEP
mysqlsqlsql-update
Mark Waugh
Mark Waugh

Reputation: 55

Update query with multiple tables and limit option

Update table2 t2
set t2.c1=t1.c1
inner join table1 t1 on ( t1.c2 = t2.c2)
where t2.c1 = "-1";

I want to execute the above query which will update the table2 column from table1 column ON INNER JOIN matching conditions. It is working fine. I am running a migration where the rows count are in million in both tables. I thought of limiting the update query in batches for query optimization but limit is not allowed in update query.

I can try with select query with limit option, but updating multiple columns would not work with this below query.

update table2 t2
set t2.c1=<?>
where t1.c2 = ( select c2 from table);

Can anyone help to use update query with optimization? Will updating millions row have any impact?

Upvotes: 1

Views: 596

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I am not sure what you really want to do, but you can update multiple columns with update and limit.

The following is fine:

update table2 t2
    set t2.c1 = <?>,
        t2.c3 = ?
    where t1.c2 = ( select c2 from table)
    limit 100;

Upvotes: 0

GMB
GMB

Reputation: 222582

You could move the limiting clause to the joined table, like so:

update table2 t2
inner join (
    select c1, c2
    from table1
    order by c2
    limit ?, ?
) t1 on t1.c2 = t2.c2
set t2.c1 = t1.c1
where t2.c1 = -1

Upvotes: 1

Related Questions