Home »

Update table using Inner Join

Start here

1. Update a value of this table to another table with inner join.

UPDATE BETA
SET BETAID B = (SELECT max(A.ALPHAID) FROM ALPHA A
WHERE B.BETAID = A.ALPHADESC)
and exists (select ‘x’ from alpha a where b.betaid=a.alphadesc);

you need the exists to ensure only rows with matching values are updated and the max to ensure that you only get one row on the updating subquery, also, the subquery has to be a ‘collated’ subquery so that it retrieves child records that match the parent.  The “and exists ….” can be left out if ou know there is a matching record in alpha for every record in beta.

2. Update command,

update (select bonus
from employee_bonus b
inner join employees e on b.employee_id = e.employee_id
where e.bonus_eligible = ‘N’) t
set t.bonus = 0

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: