SQL UPDATE statement with WHERE EXISTS

Question

Im trying to write a query that updates a date only if the group im updating has a LINE_CD of 50. Would i do it like this?

UPDATE EMPLOYER_ADDL  
SET EMPLOYER_ADDL.GTL_UW_APPRV_DT = EMPLOYER_ADDL.DNTL_UW_APPRV_DT 
WHERE EXISTS
    ( 
      SELECT EMP_PLAN_LINE_INFO.LINE_CD
      FROM EMP_PLAN_LINE_INFO
      Where EMP_PLAN_LINE_INFO.GR_NBR = EMPLOYER_ADDL.GR_NBR and
       EMP_PLAN_LINE_INFO.LINE_CD = 50
     )

You have many "right answers" good luck selecting one of them. — SQLMason, Jan 26 '12 at 19:58

score 21 · Answer 1 · answered Jan 26 '12 at 19:22

21

UPDATE ea
  SET GTL_UW_APPRV_DT = DNTL_UW_APPRV_DT
  FROM EMPLOYER_ADDL AS ea
  WHERE EXISTS
  (
    SELECT 1
      FROM EMP_PLAN_LINE_INFO AS ep
      WHERE ep.GR_NBR = ea.GR_NBR
      AND ep.LINE_CD = 50
  );

However, if you can derive this information from a query, why update the table? Seems like this will have to be run constantly else risk being out of date.

answered Jan 26 '12 at 19:22

Aaron Bertrand

272,866
37
466
490

2

you should explicitly mention the column name. What if the schema is changed in future ? So instead of 1 it should be a column name... – Pankaj Jan 26 '12 at 19:34
3

@StackOverflowUser - It won't matter for this instance. He is using `1` because `EXISTS` short circuits and we don't actually return the field. All we want to know is, does a row meet the condition listed? – JNK Jan 26 '12 at 19:36
7

Inside EXISTS the column name doesn't matter. You can say *, NULL, 'constant' etc. 1 isn't a column name. If the schema changes and you mention an explicit column name there, isn't that **less** future-proof? – Aaron Bertrand Jan 26 '12 at 19:36
ok. I want to improve myself. So if somebody explain the reason for downvote please ? – Pankaj Jan 26 '12 at 19:37
3

@StackOverflow User, I gave a reason why I downvoted your answer at the time I downvoted. You were missing a "FROM" and you do not need the alias or table name in the "SET". Please don't ask questions about your answer in another person's answer. – SQLMason Jan 26 '12 at 19:39
Constant updates - isn't that what triggers are for? – JeffO Jan 26 '12 at 19:44
Thanks for the answers guys. i should have been more specific. This query only has to run once because i need all groups that have a line_cd of 50 to have the same GTL_APPRV_DT as the DENTAL_UW_APPRV_DT before we move to production. – user1152145 Jan 26 '12 at 19:45
@Jeff O why introduce triggers if they aren't necessary, especially when they can often be problematic? – Aaron Bertrand Jan 26 '12 at 19:47
@JeffO Triggers should only be used for logging, if then. – SQLMason Jan 26 '12 at 19:55
Found and used this solution for my implementation. Seemed to make more sense to me... http://stackoverflow.com/questions/7918688/update-rows-in-one-table-with-data-from-another-table-based-on-one-column-in-eac – Bill Ortell Mar 28 '14 at 12:20
2

@BillOrtell that syntax works for Oracle, but not for SQL Server. – Aaron Bertrand Mar 28 '14 at 13:31
Gotcha, good point. In fact, I typ. use the 'where exists' but saw that other post (yes, using Oracle on my end) and it worked much smoother, faster and is more interpret-able for the person picking up that query later... Sorry for quick slight of hand. – Bill Ortell Mar 29 '14 at 01:55

score 13 · Accepted Answer · answered Jan 26 '12 at 19:24

13

What about this?

UPDATE ea  
SET ea.GTL_UW_APPRV_DT = ea.DNTL_UW_APPRV_DT 
FROM EMPLOYER_ADDL ea
    INNER JOIN EMP_PLAN_LINE_INFO ei ON(ei.GR_NBR = ea.GR_NBR)
WHERE 
ei.LINE_CD = 50

answered Jan 26 '12 at 19:24

xbrady

1,683
14
23

1

The only problem with this is extra work if the join produces duplicates (e.g. if GR_NBR is not unique in either EMPLOYER_ADDL or EMP_PLAN_LINE_INFO). I think the EXISTS format is safer because it caters to both scenarios. YMMV. – Aaron Bertrand Jan 26 '12 at 20:17
1

The GR_NBR is NOT unique in the EPLI table but it is in ADDL. By extra work do u mean the query will take longer to run? – user1152145 Jan 26 '12 at 20:28

score 7 · Answer 3 · edited Jan 26 '12 at 19:28

7

I believe this will give you same result.

UPDATE ea
  SET GTL_UW_APPRV_DT = DNTL_UW_APPRV_DT
  FROM EMPLOYER_ADDL AS ea
  INNER JOIN EMP_PLAN_LINE_INFO AS ep
      ON ep.GR_NBR = ea.GR_NBR
      AND ep.LINE_CD = 50

edited Jan 26 '12 at 19:28

Devin Burke

13,642
12
55
82

answered Jan 26 '12 at 19:24

AJP

2,125
3
16
22

4

reason for downvote please so i can learn what i am doing wrong. – AJP Jan 26 '12 at 19:33
2

I didn't do the down voting, but I think whoever did it maybe did it because your original formatting was bad? We posted almost the same thing at the same time so I don't know why you got the down vote. – xbrady Jan 26 '12 at 19:42
1

i copied it from notepad. when i added answer it screwed formating. anyw way moving on.. cheers guys. – AJP Jan 26 '12 at 19:45

score 4 · Answer 4 · answered Jan 26 '12 at 19:28

Assuming that GR_NBR is a PK and distinct in EMP_Plan_line_Info:

UPDATE  EA
SET     GTL_UW_APPRV_DT = DNTL_UW_APPRV_DT
FROM    EMPLOYER_ADDL EA
        INNER JOIN EMP_PLAN_LINE_INFO EP
            ON EP.GR_NBR = EA.GR_NBR
               AND EP.LINE_CD = 50

score 2 · Answer 5 · answered Jan 26 '12 at 19:27

2

Try with this also. I think this is new for you ?

UPDATE ADDL   
SET ADDL.GTL_UW_APPRV_DT = ADDL.DNTL_UW_APPRV_DT 
From EMPLOYER_ADDL ADDL
Inner Join  EMP_PLAN_LINE_INFO INFO on INFO.GR_NBR = ADDL.GR_NBR
Where INFO.LINE_CD = 50

answered Jan 26 '12 at 19:27

Pankaj

9,749
32
139
283

1

No FROM, and you don't need ADDL. in SET ADDL.GTL_UW_APPRV_DT = – SQLMason Jan 26 '12 at 19:30
4

@DanAndrews personally, I prefer to be explicit. What harm is there in putting the alias in the `SET` line? It's helpful for colleagues reading your code that aren't aware the alias isn't strictly necessary. If you use sensible aliases it should help document the code better, not make it more cumbersome to read and understand. – Aaron Bertrand Jan 26 '12 at 19:42
1

@Aaron Bertrand, You're setting what you're updating, there's nothing to figure out there. The main reason for the downvote was the missing FROM which is now been modified to include. – SQLMason Jan 26 '12 at 19:50
1

@AaronBertrand I don't see any value in having the alias on the SET line. It's not like it could be updating any other table. – cadrell0 Jan 26 '12 at 20:06
1

It's subjective. You don't see any value, I don't see any harm. There is no argument that it's better documented with the alias for junior folks who don't understand SQL Server's proprietary UPDATE FROM syntax. Picture an update that joins to 15 tables and the right side of the set comes from a different table. I find value in being explicit. If you don't, ok. – Aaron Bertrand Jan 26 '12 at 20:15

Brain2000 · Answer 6 · 2012-01-26T19:48:53.120

1

I believe exists requires a wildcard:

UPDATE EMPLOYER_ADDL  
SET EMPLOYER_ADDL.GTL_UW_APPRV_DT = EMPLOYER_ADDL.DNTL_UW_APPRV_DT 
WHERE EXISTS
    ( 
      SELECT *
      FROM EMP_PLAN_LINE_INFO
      Where EMP_PLAN_LINE_INFO.GR_NBR = EMPLOYER_ADDL.GR_NBR and
       EMP_PLAN_LINE_INFO.LINE_CD = 50
     )

I prefer using IN though. Some argue that it can be slower, but I have found the SQL optimizer in 2005 and higher make IN work the same as EXISTS if the field is a non-null field.

UPDATE EMPLOYER_ADDL  
SET EMPLOYER_ADDL.GTL_UW_APPRV_DT = EMPLOYER_ADDL.DNTL_UW_APPRV_DT 
WHERE EMPLOYER_ADDL.GR_NBR IN
    ( 
      SELECT EMP_PLAN_LINE_INFO.GR_NBR
      FROM EMP_PLAN_LINE_INFO
      Where EMP_PLAN_LINE_INFO.LINE_CD = 50
     )

edited Jan 26 '12 at 19:48

answered Jan 26 '12 at 19:25

Brain2000

4,655
2
27
35

2

No, `EXISTS` does not require a wildcard. The optimizer completely ignores the column list. – Aaron Bertrand Jan 26 '12 at 19:38
1

Point taken. All the exists examples I've ever seen have a wildcard but I've never thoroughly tested it. I noticed your answer just does a select 1.. interesting. – Brain2000 Jan 26 '12 at 19:52
1

The WHERE wildcard is %, the SELECT wildcard is *. Referring to all fields. – Brain2000 Jan 26 '12 at 20:07

SQL UPDATE statement with WHERE EXISTS

6 Answers6

Linked