A friendly place for programming greenhorns!
Big Moose Saloon
Register / Login
Oracle update query question
Kevin P Smith
Joined: Feb 18, 2005
Jan 23, 2009 07:27:53
Probably not the ideal forum to post this, but I know you tend to get quick resulst here so I thought I'd give it a go.
I have a tabel where I need to update a column, but I can only find the ID of that record by search a second table.
Basically the pseudo code query is...
Update table1 status='Effective' where table ID = X, X = table 1 child_id where table 2 master_id = '123456'
This is being executed on an Oracle DB.
Here is my attempt at the query, but I get a
ORA-00933: SQL command not properly ended
UPDATE table1 a SET a.status='Effective' INNER JOIN table2 b ON a.id = b.child_id WHERE b.master_id = '123456'
author & internet detective
Joined: May 26, 2003
Jan 23, 2009 18:37:58
This forum is fine - it's about SQL.
I remember having to do a nested query for the other table in updates in Oracle for it to work. It's been a while, but
this looks familiar
How To Ask Questions The Smart Way
Blogging on Certs:
SCEA Part 1
Part 2 & 3
Core Spring 3
TOGAF part 1
Joined: Oct 06, 2007
Jan 24, 2009 12:57:11
update table1 SET a.status='Effective' from table1 a inner join table2 b on b.child_id = a.id WHERE b.master_id = '123456' ;
or alternatively you can do this
update ( select status from table1 a inner join table2 b on a.id = b.child_id WHERE b.master_id = '123456') c set c.status='Effective' ;
It is sorta covered in the
JavaRanch Style Guide
subject: Oracle update query question
Update with subquery
Update with FROM table concept
Getting lock on DB from two different JVM
update query not working properly, using jsp
Hibernate: Different Join in Save() and Get()
All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter
| Powered by
Copyright © 1998-2014