Updating multiple records sql

Rated 3.93/5 based on 526 customer reviews

If only one of these is the PK, then add the other field to the UPDATE list.

If neither of them is a primary key (that seems unlikely) then this approach will always create new records - probably not what is wanted.

The dominant factor in the time taken to complete the overall operation tends to be the “admin” work in conveying the application’s intention to the database server rather than the actual updates to the database.

A more effective solution to this problem is to attempt to reduce the number of UPDATE statements.

A requirement arises in many systems to update multiple SQL database rows.

For small numbers of rows requiring updates, it can be adequate to use an UPDATE statement for each row that requires an update.

Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).The solution is everywhere but to me it looks difficult to understand.For instance, three updates into 1 query: UPDATE table_users SET cod_user = '622057' , date = '12082014' WHERE user_rol = 'student' AND cod_office = '17389551'; UPDATE table_users SET cod_user = '2913659' , date = '12082014' WHERE user_rol = 'assistant' AND cod_office = '17389551'; UPDATE table_users SET cod_user = '6160230' , date = '12082014' WHERE user_rol = 'admin' AND cod_office = '17389551'; 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'; My SQL allows a more readable way to combine multiple updates into a single query.Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.Visit Stack Exchange Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Sign up to join this community Table1 ---- ------- id | Date ---- ------- 1 null 1 null 2 null 2 null 2 null 2 null 3 null 4 null 4 null Table2 ---- ------- id | Date ---- ------- 1 2013-01-29 .000 1 2013-01-29 .000 2 2013-01-31 .000 2 2013-03-11 .000 2 2013-01-31 .000 2 2013-01-31 .000 3 2013-02-28 .000 4 2013-01-31 .000 4 2013-01-31 .000 Table1 ---- ------- id | Date ---- ------- 1 2013-01-29 .000 1 2013-01-29 .000 2 2013-01-31 .000 2 2013-03-11 .000 2 2013-01-31 .000 2 2013-01-31 .000 3 2013-02-28 .000 4 2013-01-31 .000 4 2013-01-31 .000 create table Table1(id int, Date datetime null); create table Table2(id int, Date datetime); insert Table1 values (1,null) insert Table1 values (1,null) insert Table1 values (2,null) insert Table1 values (2,null) insert Table1 values (2,null); insert Table2 values (1,'2013-01-29 .000') insert Table2 values (1,'2013-01-29 .000') insert Table2 values (2,'2013-01-31 .000') insert Table2 values (2,'2013-03-11 .000') insert Table2 values (2,'2013-01-31 .000'); Keeping in mind that we haven't been provided (yet) with any means to determine which rows to match between Table1 and Table2 for a given id value, I'll just let row_number() generate a 'matching' rowid.

Leave a Reply