Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL UPDATE statement with WHERE EXISTS

Tags:

sql

sql-server

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
     )
like image 661
user1152145 Avatar asked Jan 26 '12 19:01

user1152145


People also ask

Can we use exists in update in SQL?

The SQL Server (Transact-SQL) EXISTS condition is used in combination with a subquery and is considered to be met if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Can we use update with WHERE clause?

Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!

Can we have subquery in update statement?

Like SELECT , the UPDATE statement can have a subquery in several places or clauses. In an UPDATE , the two clauses in which subqueries are used most commonly are SET and WHERE . The SET clause is where we define the new value for the column being modified by the UPDATE .


1 Answers

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.

like image 60
Aaron Bertrand Avatar answered Oct 24 '22 22:10

Aaron Bertrand