How to update a table's column value from a different table's same column values?

Question

I have two tables.

template table.(templ_id and obj_id is primary key)

templ_id    obj_id

TP000002    PE554959
TP000003    PE555867
TP000006    PE555102
TP000009    PE554994
TP000009    PE554956
TP000009    PE555176
TP000009    PE555598
TP000009    PE555256
TP000010    PE555297
TP000010    PE555286

Business table.(bsn_no is primary key)

bsn_no    obj_id     templ_id
1         PE554959   null
2         PE555867   null
3         PE555102   null
4         PE554994   null
5         PE554956   null
6         PE555176   null
7         PE555598   null
8         PE555256   null
9         PE555297   null
10        PE555286   null

I want to update business tables templ_id from template table's templ_id based on the obj_id using a single update query.

Then look at this solution for Oracle specific: http://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table — Vincent James, Feb 26 '16 at 12:14

Gordon Linoff · Answer 1 · 2016-02-27T21:51:58.340

1

You can do this with a correlated subquery:

update bsn_no
    set templ_id = (select t.templ_id
                    from template_table t
                    where t.obj_id = bsn_no.obj_id
                   );

This is standard SQL and should work in any database (although if you have duplicate obj_id in the template_table, it will return errors). Specific databases have other syntax for combining tables in an update.

EDIT:

If this returns multiple rows, the simplest solution is an aggregation without group by or using where rownum = 1:

update bsn_no
    set templ_id = (select t.templ_id
                    from template_table t
                    where t.obj_id = bsn_no.obj_id and rownum = 1
                   );

This avoids the error, by choosing an (arbitrary) matching value.

edited Feb 27 '16 at 21:51

answered Feb 26 '16 at 12:04

Gordon Linoff

1,242,037
58
646
786

it does not have any duplicate obj_id. it has duplicate temp_id. – user3040157 Feb 26 '16 at 12:10
if i run your query it has thrown the below error message. SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row" – user3040157 Feb 26 '16 at 12:13
@user3040157 . . . It must have duplicate `obj_id` (which is the `join` key) if it is returning duplicate results. – Gordon Linoff Feb 27 '16 at 02:53
With the rownum solution you MUST be positive about the duplicate data IDs, he was not. The group solution would have still thrown an error instead of creating a bunch of bad data. I considered TOP 1 in the sub query to accomplish the same thing but wasn't sure about his data, so went with the GROUP. I was the +1 for explaining the duplicate key situation to him. – Vincent James Feb 27 '16 at 12:56
@VincentJames . . . I think you should explain that reasoning in your answer. – Gordon Linoff Feb 27 '16 at 21:51

Vincent James · Accepted Answer · 2016-02-26T12:21:52.863

1

UPDATE BusinessTable
SET BusinessTable.templ_id = (SELECT TemplateTable.templ_id
                               FROM TemplateTable
                               WHERE BusinessTable.obj_id = TemplateTable.obj.id )

IF the IDs are the same you can group the subquery:

UPDATE BusinessTable
SET BusinessTable.templ_id = (SELECT TemplateTable.templ_id
                               FROM TemplateTable
                               WHERE BusinessTable.obj_id = TemplateTable.obj.id 
                               GROUP BY Template.templ_id
                               )

edited Feb 26 '16 at 12:21

answered Feb 26 '16 at 12:05

Vincent James

1,120
3
16
27

i have tried with this code. but it has thrown the below error SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row" – user3040157 Feb 26 '16 at 12:15
That is what he was talking about. There cant be multiple records with the same tmpl_id in the Template table, which the error indicates there is. This means there is not a 1 to 1 relationship between the tables, it is a 1 to many. This means it doesn't know which value to put in of the many values returned. – Vincent James Feb 26 '16 at 12:16
IF the tmpl_id values in the template table are all the same you can group the results. But for this to work, they tmpl_id returned in all records must be the same. Run the select query without the UPDATE and look at the tmpl IDs in the records and see if they vary. – Vincent James Feb 26 '16 at 12:19
i have run the select query it is showing correct result. – user3040157 Feb 26 '16 at 12:23
Then try the new one I posted with the GROUP By statement and see if it throws the same error. – Vincent James Feb 26 '16 at 12:24
Did you try the GROUP BY query? – Vincent James Feb 26 '16 at 12:38
Thanks once again sir. – user3040157 Feb 26 '16 at 12:44

score 0 · Answer 3 · answered Feb 26 '16 at 12:19

0

UPDATE BT
SET templ_ID = TT.templ_ID
FROM [Business Table] BT
INNER JOIN [Template Table] TT ON BT.obj_id = TT.obj_id

answered Feb 26 '16 at 12:19

Richard Boyce

413
5
12

How to update a table's column value from a different table's same column values?

3 Answers3