Change Select statement to an update statement

Question

SELECT A.GRPNO, A.EMPNO, A.DEPNO, A.PENDCD FROM EMPDEP A, EEDPELIG B
WHERE A.GRPNO=B.GRPNO
AND A.EMPNO=B.EMPNO
AND A.DEPNO=B.DEPNO
AND A.GRPNO = 6606 AND A.SPOUSE = 'T'
AND B.ELIGFLAG01 = 'T' AND SNAPTHRUDT ='DEC312999'

Our selection statement has been successful at pulling the information we need however we're new with SQL and are struggling to create an update statement that is replacing the "a.pendcd=0" to "a.pendcd=20" from the information in the select statement. Any help is appreciated, thank you.

Tag your question with the database you are using. Why are you using archaic `JOIN` syntax? Why are you using meaningless table aliases? — Gordon Linoff, Jan 09 '20 at 22:58
Does this answer your question? [How can I do an UPDATE statement with JOIN in SQL?](https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) — Nicholas Carey, Jan 09 '20 at 23:21

score 0 · Answer 1 · answered Jan 10 '20 at 00:36

0

update a
a.pendcd=20
FROM EMPDEP A inner join EEDPELIG B
on A.GRPNO=B.GRPNO
AND A.EMPNO=B.EMPNO
AND A.DEPNO=B.DEPNO
AND A.GRPNO = 6606 AND A.SPOUSE = 'T'
AND B.ELIGFLAG01 = 'T' AND SNAPTHRUDT ='DEC312999'
where a.pendcd=0

answered Jan 10 '20 at 00:36

Shep

638
3
15

score 0 · Answer 2 · answered Jan 10 '20 at 02:24

Oracle does not support FROM or JOIN in UPDATE (under most circumstances).

Just use EXISTS:

UPDATE EMPDEP ed
    SET  . . .
    WHERE EXISTS (SELECT 1
                  FROM EEDPELIG p
                  WHERE ed.GRPNO = p.GRPNO AND
                        ed.EMPNO= p.EMPNO AND
                        ed.DEPNO= p.DEPNO AND
                        p.ELIGFLAG01 = 'T'
                 )
          ed.GRPNO = 6606 AND
          ed.SPOUSE = 'T' AND
          ed.SNAPTHRUDT ='DEC312999';

It is unclear if the condition on SNAPTHRUDT is on the outer table or inner table. If it is on p, then move it to the subquery.

score 0 · Answer 3 · answered Jan 10 '20 at 02:45

You can use MERGE statement as following:

Lets assume EMPDEP table has primary key which is EMPDEP_UID.

MERGE INTO EMPDEP TRG
USING
(SELECT A.EMPDEP_UID, A.PENDCD
   FROM EMPDEP A, EEDPELIG B
  WHERE A.GRPNO=B.GRPNO
    AND A.EMPNO=B.EMPNO
    AND A.DEPNO=B.DEPNO
    AND A.GRPNO = 6606 
    AND A.SPOUSE = 'T'
    AND B.ELIGFLAG01 = 'T' 
    AND SNAPTHRUDT ='DEC312999') SRC
ON (TRG.EMPDEP_UID = SRC.EMPDEP_UID)
WHEN MATCHED THEN
UPDATE SET TRG.PENDCD = 0
WHERE TRG.PENCD = 20;

You can use unique keys instead of primary key to identify the records to be updated. But it is safe to use primary key as unique key can contain nulls which can change the behaviour of our query.

Cheers!!

Change Select statement to an update statement

3 Answers3