Updating multiple rows at a time in sql

14 Mar

Now you can ask questions directly to me on my new site ASPForums. Net Question Answer site, specially started to allow visitors of this site ask their doubts and queries. Just click the Ask Now button and ask your question now.Consider the following code: The above code updates 10000 rows at a time and the loop continues till @@rowcount has a value greater than zero. Best practices while updating large tables in SQL Server1.Always use a WHERE clause to limit the data that is to be updated2.

You can manipulate Oracle data flexibly and safely because PL/SQL fully supports all SQL data manipulation statements (except ), transaction control statements, functions, pseudocolumns, and operators.For additional information on assigning values to PL/SQL variables, see "Assigning a SQL Query Result to a PL/SQL Variable".Oracle is transaction oriented; that is, Oracle uses transactions to ensure data integrity.CREATE TABLE public.dummy ( address_id SERIAL, addr1 character(40), addr2 character(40), city character(25), state character(2), zip character(5), customer boolean, supplier boolean, partner boolean ) WITH ( OIDS=FALSE ); insert into address customer,supplier,partner SELECT case when cust.addr1 is not null then TRUE else FALSE end customer, case when suppl.addr1 is not null then TRUE else FALSE end supplier, case when partn.addr1 is not null then TRUE else FALSE end partner from ( SELECT * from address) pa left outer join cust_original cust on (pa.addr1=cust.addr1 and pa.addr2=cust.addr2 and pa.city=and pa.state=cust.state and substring(cust.zip,1,5) = ) left outer join supp_original suppl on (pa.addr1=suppl.addr1 and pa.addr2=suppl.addr2 and pa.city=and pa.state=suppl.state and = substring(suppl.zip,1,5)) left outer join partner_original partn on (pa.addr1=partn.addr1 and pa.addr2=partn.addr2 and pa.city=and pa.state=partn.state and = substring(partn.zip,1,5) ) where pa.address_id = address_id UPDATE dummy SET customer=subquery.customer, address=subquery.address, partn=subquery.partn FROM (SELECT address_id, customer, address, partn FROM /* big hairy SQL */ ...) AS subquery WHERE dummy.address_id=subquery.address_id; FWIW, Oracle does accept that basic construct, however the performance of the update tends to degrade severely as the tables get larger. though as Oracle also supports the MERGE statement.A requirement arises in many systems to update multiple SQL database rows.But we can group the updates according to the value being set, and then do one UPDATE statement per distinct value.Microsoft is conducting an online survey to understand your opinion of the Msdn Web site.UPDATE example_table SET variable1 = 12 WHERE id=1; UPDATE example_table SET variable1 = 42 WHERE id=2; UPDATE example_table SET variable1 = 32 WHERE id=3; UPDATE example_table SET variable1 = 51 WHERE id=4; This query update the porderitems table (purchase order lines), setting the currency to -2 for all the lines connected to purchase order 40396.The query neither knows nor cares how many lines there are in that purchase order; all of them will be updated.See Chapter 7, "Performing SQL Operations with Native Dynamic SQL".CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name FROM employees; DECLARE emp_id employees_temp.employee_id%TYPE; emp_first_name employees_temp.first_name%TYPE; emp_last_name employees_temp.last_name%TYPE; BEGIN INSERT INTO employees_temp VALUES(299, 'Bob', 'Henry'); UPDATE employees_temp SET first_name = 'Robert' WHERE employee_id = 299; DELETE FROM employees_temp WHERE employee_id = 299 RETURNING first_name, last_name INTO emp_first_name, emp_last_name; COMMIT; DBMS_OUTPUT.