How to update a table while joining based on a condition?

Question

CREATE OR REPLACE PROCEDURE PRC_TWO_DAY_EXTEND 
AS 
BEGIN
    UPDATE CHECKOUT
    SET CHECK_IN_DATE = CHECK_IN_DATE + 2 
    JOIN PATRON ON PATRON.PAT_ID = CHECKOUT.PAT_ID
    FROM CHECKOUT  
    WHERE PATRON.PAT_TYPE = 'STUDENT';
END;

EXEC PRC_TWO_DAY_EXTEND;

Basically I need to just add two days to the check in date but I cant figure out how to join them and then update them, because its based on whether or not PAT_TYPE = 'STUDENT'

MySQL or Oracle? Please tag only one database. – GMB Nov 17 '20 at 00:01 — GMB, Nov 17 '20 at 00:01

score 1 · Answer 1 · answered Nov 17 '20 at 00:02

1

Your date arithmetics suggests Oracle, so let me assume that's the database you are running.

In that database, you could phrase the query as:

update checkout c
set check_in_date = check_in_date + 2
where exists (
    select 1
    from patron p
    where p.pat_id = c.pat_id and p.pat_type = 'STUDENT'
)

This updates the check-in date of rows whose pat_id can be found in patron with a pat_type having value 'STUDENT'.

answered Nov 17 '20 at 00:02

GMB

195,563
23
62
110

Thank you, I do not have enough reputation to up vote this but it worked! – Alex Walker Nov 17 '20 at 00:06
1

@AlexWalker: it seems like you now have reached the reputation threshold that allows upvoting. And, as the asker of the question, you can accept the answer that you like most, regardless of your reputation. See: https://stackoverflow.com/help/someone-answers. – GMB Nov 17 '20 at 00:13

score 1 · Answer 2 · answered Nov 17 '20 at 03:49

1

Yet another option is to use the MERGE as follows:

Merge into checkout c
Using
(select pat_id 
   From patron
  Where pat_type = 'STUDENT') p
On (p.pat_id = c.pat_id)
When matched then
Update set c.check_in_date = c.check_in_date + 2

answered Nov 17 '20 at 03:49

Popeye

34,995
4
9
31

score -1 · Answer 3 · answered Nov 17 '20 at 00:08

Assuming Oracle, and assuming PATRON_ID is primary key in table PATRON, you can update through join, like this:

update
  ( select c.check_in_date
    from   checkout c join patron p on p.pat_id   = c.pat_id
                                   and p.pat_type = 'STUDENT'
  )
set check_in_date = check_in_date + 2
;

How to update a table while joining based on a condition?

3 Answers3