Table1 columns: id, name
Table2 columns: id, status.
How to make request that update Table1.name for Table1.id = 'some_id' if Table2.id = Table1.id and Table2.status = 'some_status' ?
Thanks in advance.
Asked
Active
Viewed 74 times
0
-
Why do you need that in a separate table? Does it work for you to have a single table with "id, name, status" columns? – carlosherrera Oct 30 '14 at 14:46
-
The structure of tables can't be changed. – slider Oct 30 '14 at 14:48
-
1Check out this [question](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match). – programmer43229 Oct 30 '14 at 14:51
-
What did you try so far? And, Update what in table1? The matching ID itself? – Lalit Kumar B Oct 30 '14 at 14:53
-
Watch a question: need to update Table1.name field. + i tried INNER JOIN in UPDATE, but it's not work in Oracle. – slider Oct 30 '14 at 15:00
2 Answers
1
update Table1 set Table1.name = 'XXX'
where Table1.id = 'some_id'
and exists (select 1 from Table2 where Table2.id = Table1.id and Table2.status = 'some_status');
The EXISTS keyword tests for the existence of any rows in a subquery. If no rows are found, the answer is FALSE. Otherwise, the subquery returns TRUE. NOT EXISTS reverses the results.
Note that the entire subquery is executed, even though EXISTS need only know whether or not the subquery returns any rows — so beware using EXISTS with subqueries that return large numbers of rows.
It’s worth noting that this sort of query is sometimes referred to as a “semijoin”. A semijoin is a SELECT statement that uses the EXISTS keyword to compare rows in a table with rows in another table.
In addition to that it's a correlated query which is executing for each row of Table1.
EXISTS may replace IN in many cases and often is more beneficial than IN.
Multisync
- 8,657
- 1
- 16
- 20
-
Please add some explanation to your code. Code-only answers are sometimes good enough, but code+explanation answers are always better – Barranka Oct 30 '14 at 15:49
-
0
try something like this
update table1
set table1.name = 'newName'
FROM table1 inner join table2 on table1.id = table2.id
where table2.status = 1enter code here
Pedro Adão
- 36
- 4