CODEWITHSUNDEEP
sqloracle-database
Negashion
Negashion

Reputation: 139

Is there a better way to update same field in multiple tables?

I have 10 tables, and i want to update the same field in all of them with the same value. The update takes as condition a field which is primary key in one table, foreign in others and there are some tables where it isnt even a key. For example :

  TABLEX               TABLEY                 TABLEZ         

  FIELD1 (PK)          FIELD1(FK)             FIELD1               
  FIELD3               FIELD3                 FIELD3

I want to update FIELD3 in all tables with the same value for the same FIELD1. I have been told to use a stored procedure using cursors a for loops to update the tables and after investigating a bit i have read that cursors aint very time efficient. I was wondering if this is the best way or if you know of a better approach. Im fairly new to sql.

Thx in advance.

Upvotes: 1

Views: 338

Answers (1)

Andrew G
Andrew G

Reputation: 2496

You really don't need to use cursor loops - a simple SQL UPDATE statement will work just as fine on all 10 tables. However, you do have to issue 10 separate UPDATE statements, there is no way around that.

Note: if you used dynamic SQL, you could end up with a little bit less code but I'd very much recommend against it for a host of reasons: code readability, disrupts built-in SQL object dependency tracking, SQL injection possibilities...)

Upvotes: 2

Related Questions