Update multiple records with select statement

Question

I'm wondering that how to update multiple records together with select statement.

I want to do this way:

Update table set new_col = (select col1 from tab1, tab2 where tab1.id =tab2.neid)

here select subquery returns around 100 records, and for those 100 records I'd like to update my new_col.

Is there any way to fulfill this task?

I tried using update select, but didn't succeed.

Sample:

Table contains around 10 records with 3 columns. For an instance, I'd like to add one more column to the existing table.

Now I have 4 columns. New added column is supposed to get the values for these 10 records from another table.

So how would I do this?

Thanks

Please edit your question and provide sample data and desired results. Your question is too ambiguous. — Gordon Linoff, Mar 30 '16 at 11:01
@inityk You've updated your question, but you haven't provided us any extra information that tells us what you're trying to do. Please update your question to provide sample data in your tables along with the output you're expecting to see after your update. ([Here's an example of a question](http://stackoverflow.com/questions/31852230/calculation-in-two-columns-of-different-row-in-sql) which does just that.) — Boneist, Mar 30 '16 at 11:23

score 0 · Answer 1 · answered Mar 30 '16 at 11:03

In Oracle, you can do this using a correlated subquery. You need some column to determine which row to update:

Update table t
    set new_col = (select col1
                   from tab1 join
                        tab2 
                        on tab1.id = tab2.neid
                   where tab?.?? = t.??
                  )
    where exists (select 1
                  from tab1 join
                       tab2 
                       on tab1.id = tab2.neid
                  where tab?.?? = t.??
                 );

You question doesn't specify how to identify the rows in the outer table that match each row in the subquery. Hence, the ? and ??.

Update multiple records with select statement

1 Answers1