Update rows based on another table

Question

I have a table that keeps track of the physical mass of sprockets in my inventory.

create table sprockets(
    id NUMBER,
    mass NUMBER
);

INSERT into sprockets VALUES (1, 4);
INSERT into sprockets VALUES (2, 8);
INSERT into sprockets VALUES (3, 15);
INSERT into sprockets VALUES (4, 16);
INSERT into sprockets VALUES (5, 23);
INSERT into sprockets VALUES (6, 42);

I employ sprocket mechanics to perform routine maintenance on my sprockets. If their modifications make the sprocket's mass change, they make a note of it in their maintenance report.

create table maintenance_events(
    sprocket_id NUMBER,
    new_mass NUMBER
);

--chipped a widget off of sprocket #1; mass reduced to 3 kg
INSERT into maintenance_events VALUES (1, 3);       
--new lead bearings makes sprocket #2 weigh 413 kg
INSERT into maintenance_events VALUES (2, 413);

I want to keep the sprockets table updated with the current mass of each sprocket. I want to take the new_mass in maintenance_events and overwrite the old mass values in sprockets. I referred to the top two answers from this question, but both give errors.

UPDATE sprockets
set mass = maintenance_events.new_mass
from sprockets, maintenance_events
where sprockets.id = maintenance_events.sprocket_id

Error at Command Line:2 Column:38
Error report:
SQL Error: ORA-00933: SQL command not properly ended

UPDATE sprockets
set sprockets.mass = maintenance_events.new_mass
from sprockets
INNER JOIN maintenance_events
on sprockets.id = maintenance_events.sprocket_id

Error at Command Line:2 Column:48
Error report:
SQL Error: ORA-00933: SQL command not properly ended

What am I doing wrong?

unless you only keep the latest maintenance event for each sprocket, you may also want to store a date in that table, so that you can be sure you are using the most current value for that sprocket — Joe, Aug 28 '12 at 19:34
Possible duplicate: http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join — Esoteric Screen Name, Aug 28 '12 at 19:36
Are all statements terminated by a semicolon? I found [this link](http://www.dba-oracle.com/t_ora_00933_sql_command_ended.htm) — swasheck, Aug 28 '12 at 19:36
@Joe, That's on my list for the 2.0 release. Currently, my maintenance data set is guaranteed to have unique sprocket ids. — Kevin, Aug 28 '12 at 19:37
The linked Q is about SQL Server. Oracle doesn't support 'UPDATE...FROM..." — rfusca, Aug 28 '12 at 19:49
Oh, I thought sql worked the same, no matter what thingy you used to run it. You learn something new every day. — Kevin, Aug 28 '12 at 19:56
@rfusca You can definitely write UPDATE FROM statement in SQL Server. I have done them in 2005 & 2008. See this link http://www.bennadel.com/blog/938-Using-A-SQL-JOIN-In-A-SQL-UPDATE-Statement-Thanks-John-Eric-.htm — DeanOC, Aug 28 '12 at 20:35
@DeanOC ...I think you misunderstoodl. I didn't say you couldn't in SQL Server - I said you can't in Oracle. — rfusca, Aug 29 '12 at 02:17

score 3 · Accepted Answer · answered Aug 28 '12 at 19:51

3

This is what merge, an upsert, is for:

merge into sprockets s
using ( select * from maintenance_events ) m
on (s.id = m.sprocket_id)
when matched then
 update 
    set s.mass = m.new_mass
        ;

It's far more efficient than doing multiple table-scans with where not exists etc.

Here's a SQL Fiddle to prove that it works.

answered Aug 28 '12 at 19:51

Ben

51,770
36
127
149

What you need to do in your query for the merge though is to have some kind of timestamp field or such and only get the latest record for each sprocket_id. – rfusca Aug 28 '12 at 20:05
@rfusca, you don't _need_ to, only if there's more than one in `maintenance_events`, but there is no timestamp in this table. You could easily though add `rank() over ( partition by sprocket_id order by new_mass ) as rnk` or similar to the `select` and `rnk = 1` to the `on` clause to get round it though. – Ben Aug 28 '12 at 20:07
Well I'm assuming his real world maintenance_event is going to have more than one record per sprocket, otherwise just update the sprocket table. – rfusca Aug 28 '12 at 20:09
And using rank is arbitrary within the confines of the question but silly in the real world. There's no reason to think that the mass of a sprocket would only ever increase or decrease. – rfusca Aug 28 '12 at 20:10
@rfusca, that's my point... as there is no timestamp in the table your question is completely indeterminate without it. `merge`, will work in exactly the same was as the other queries given, failing if there's more than one `sprocket_id`. However, it's more efficient. – Ben Aug 28 '12 at 20:12
Yes, there should probably be a timestamp in the table, but neither of us have any idea of the OPs business rules or how appropriate that would be. – Ben Aug 28 '12 at 20:14
Since you're wondering, my real-world `maintenance_event` table actually does guarantee uniqueness of sprocket_id, so this solution works as-is. I have a timestamp column too, if we ever ditch the uniqueness constraint for whatever reason. – Kevin Aug 28 '12 at 20:17
I should have been more clear then, my comment wasn't meant to imply that MERGE wasn't appropriate - but that for it to apply in any kind of realistic situation based on the OP's example, it needs more information. – rfusca Aug 28 '12 at 20:17
Yeah, it's a legacy system thing. I blame the last guy who worked on this. – Kevin Aug 28 '12 at 20:19
:-), not that odd. It depends what the database is doing. I have a few tables that are completely unique on the same PK as another table. The idea is that instead of doing an index fast full scan of a 200m row table you do one of a 1m row table and then join to the 200m row. When doing massively over-complicated things it saves a lot of time – Ben Aug 28 '12 at 20:20

swasheck · Answer 2 · 2012-08-28T19:41:30.520

2

What about this?

UPDATE sprockets
SET sprockets.mass = (select new_mass 
                           from maintenance_events 
                      where sprockets.id = maintenance_events.sprocket_id)
WHERE EXISTS (select new_mass 
                           from maintenance_events 
                      where sprockets.id = maintenance_events.sprocket_id);

edited Aug 28 '12 at 19:41

answered Aug 28 '12 at 19:23

swasheck

4,644
2
29
56

It says, Error at Command Line:1 Column:16 SQL Error: ORA-00971: missing SET keyword – Kevin Aug 28 '12 at 19:26
whoopsie. try now. i must've gotten confused with my WHERE and SET placement – swasheck Aug 28 '12 at 19:27
Now I'm getting the error I got from my first two attempts, "Sql command not properly ended" – Kevin Aug 28 '12 at 19:30
2

If you have more than one record for a sprocket_id then this will break. – rfusca Aug 28 '12 at 20:01

heretolearn · Answer 3 · 2012-08-28T19:51:56.597

1

Try this:

UPDATE sprockets
set mass = (select maintenance_events.new_mass
from maintenance_events
where sprockets.id = maintenance_events.sprocket_id) where exists 
(select maintenance_events.sprocket_id from maintenance_events 
 where sprockets.id = maintenance_events.sprocket_id);

You can check it here: http://sqlfiddle.com/#!5/f4262/11/0

edited Aug 28 '12 at 19:51

answered Aug 28 '12 at 19:28

heretolearn

6,387
4
30
53

This partially works, in that it sets sprocket 1's mass to 5 as expected. However, all my other masses have been set to null! Good thing I made a back up :-) – Kevin Aug 28 '12 at 19:33
Yes, this query does not contain a filter – swasheck Aug 28 '12 at 19:35

Update rows based on another table

3 Answers3