We have a very repetitive need to execute UPDATE commands in our Database where only one table is updated, but two additional tables need to be referenced. I've been doing SQL for a long time, but have always seemed to just stick with the basics. I've never used JOINs before. Indexes aside, I'm thinking my SQL in general is inefficient and was hoping if someone could let me know if they think Statement 2 below is not only identical to Statement 1 (currently using this format), but is also more efficient?

Statement 1

UPDATE TABLE_A, TABLE_V, TABLE_L
   SET TABLE_A.COL_1 = TABLE_V.COL_3 
 WHERE TABLE_A.COL_2 = 'ABC' 
   AND TABLE_L.COL_6 = 'ABC'
   AND TABLE_A.COL_1 = TABLE_V.COL_4
   AND TABLE_L.COL_7 = TABLE_V.COL_5
   AND TABLE_L.COL_8 = TABLE_V.COL_4;

Statement 2

UPDATE A
   SET COL_1 = COL_3
  FROM TABLE_A AS A
  JOIN TABLE_V AS V ON A.COL_1 = V.COL_4
  JOIN TABLE_L AS L ON (L.COL_7 = V.ID AND L.COL_8 = V.COL_4)
 WHERE COL_2 = 'ABC' AND COL_6 = 'ABC';

Any help would be greatly appreciated :)

Anthony Mangione (amangione@jatsoftware.com)

asked 11 Nov '10, 16:18

Anthony%20Mangione's gravatar image

Anthony Mang...
26113
accept rate: 0%

edited 11 Nov '10, 17:43

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270

While I would strongly recommend the 2nd form, the 1st form seems to have some errors (e.g. a doubled "AND"). Furthermore, it's hard to tell whether COL_3 is only existing in TABLE_V without knowing the database schema... Could you re-check these statements and possibly format them a bit more similar?

(11 Nov '10, 16:39) Volker Barth

To be semantically equivalent, the second query should read

UPDATE Table_A as A
   SET COL_1 = COL_3
  FROM TABLE_A AS A
  JOIN TABLE_V AS V ON A.COL_1 = V.COL_4
  JOIN TABLE_L AS L ON (L.COL_7 = V.COL_5 AND L.COL_8 = V.COL_4)
 WHERE COL_2 = 'ABC' AND COL_6 = 'ABC';

As you surmised, the two UPDATE statements are semantically equivalent (if the latter is rewritten as above). They are also both optimized in exactly the same way - neither will be more efficient than the other.

You can determine the access plan used by either statement by viewing the graphical plan for the statement via DBISQL (SHIFT+F5). To improve performance, appropriate indexes may need to be defined on the columns involved in joining the three tables together, or as well on one or both of COL_2 and COL_6 to efficiently support the comparisons with the string literals "ABC".

permanent link

answered 11 Nov '10, 21:07

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

@Glenn: Would the first query fail whereas the second would work when the user is missing UPDATE permission on TABLE_V and TABLE_L? Or are such permission checks done after the statement is rewritten (when it's known that there's no attempt to update those tables)?

(12 Nov '10, 08:22) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×242
×90
×28
×23

question asked: 11 Nov '10, 16:18

question was seen: 1,360 times

last updated: 11 Nov '10, 21:07