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

216,147
25
84
135

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

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

35,427
4
10
31

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

2 Answers2