Oracle sql updating multiple tables Sex achern

Rated 4.18/5 based on 946 customer reviews

Thus, we need an update statement that is efficient.Because Oracle does not allow multiple tables within the same update statement, I know at least one subquery will be needed.

In recent times I have seen a developer writing a cursor to update a table. We want to update the value from Table2 to Table1 for the rows where Col1 is 21 and 31.In order for the “best practice” update statement to work, they must be either primary keys or unique indexes, otherwise the query will fail with the error message “ORA-01779: Cannot modify a column which maps to a non key-preserved table”.As an aside, to create primary key or unique indexes, follow the examples below.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 CREATE TABLE one ( record_id SERIAL, one_first_var INTEGER, one_second_var INTEGER, one_third_var INTEGER ); INSERT INTO one (one_first_var, one_second_var, one_third_var) VALUES (1, 1, 1); INSERT INTO one (one_first_var, one_second_var, one_third_var) VALUES (2, 2, 2); INSERT INTO one (one_first_var, one_second_var, one_third_var) VALUES (3, 3, 3); CREATE TABLE two ( record_id SERIAL, two_first_var INTEGER, two_second_var INTEGER, two_third_var INTEGER ); INSERT INTO two (two_first_var, two_second_var, two_third_var) VALUES (21, 21, 21); INSERT INTO two (two_first_var, two_second_var, two_third_var) VALUES (22, 22, 22); INSERT INTO two (two_first_var, two_second_var, two_third_var) VALUES (23, 23, 23); WITH my_values AS ( SELECT one_first_var, one_second_var, one_third_var FROM one WHERE one_first_var = 2 ) UPDATE two SET two_first_var = my_values.one_first_var, two_second_var = my_values.one_second_var, two_third_var = my_values.one_third_var FROM my_values WHERE two_second_var = 22; UPDATE tgt SET Column1 = src.

Leave a Reply