1

I'm attempting to update a table using another table in SQL Developer. The data structure looks like this:

Table_to_be_updtated

Shipment    pkg_type.
1395         1
1395         1
1395         1
2233         2
5466         3

Table_with_update_info

Shipment       pkg_type.
1395            3
1395            3
1395            3
2233            1
5466            2

This is my updater query

UPDATE d 
SET d.pkg_type = bd.pkg_type 
FROM Table_to_be_updtated.Shipment d
JOIN Table_with_update_info.Shipment bd
ON bd.Shipment = d.Shipment;

This is only my most recent attempt. I've tried may other versions of doing the update query and no success. I hope someone can help me.

Thank you

Fdxie
  • 11
  • 2
  • 1
    sql developer is an oracle tool,are you sure your tag is right? – Mihai Jan 21 '16 at 14:37
  • Fdxie, welcome to StackOverflow. the `[sql-server]` tag is specific to Microsoft SQL Server. SQL Developer and Oracle have their own tags. I've retagged your post based on the content, but please be aware of this for the future. It's also likely that answerers will want t oknow what version of Oracle you're using. – Dan Field Jan 21 '16 at 14:44
  • `FROM` is invalid in an `UPDATE` statement –  Jan 21 '16 at 15:44

1 Answers1

1

For starters, it looks like you are referencing columns in your table declarations, but regardless of that - the easiest way to do this in Oracle is with the following syntax:

UPDATE Table_to_be_updtated d
   SET pkg_type = (SELECT bd.pkg_type 
                     FROM Table_with_update_info bd
                    WHERE bd.Shipment = d.Shipment);

For other means, looks at Justin`s answer on correlated updates here:

Community
  • 1
  • 1
Michael Broughton
  • 4,045
  • 14
  • 12
  • I tried your recommendation above and I get the following error: SQL Error: ORA-01427: single-row subquery returns more than one row – Fdxie Jan 21 '16 at 19:27
  • Also if it helps there are records in Table_to_be_updated that will not be changed due to no match in the Table_with_update_info. – Fdxie Jan 21 '16 at 19:34
  • I'm am using Oracle SQL Developer version 3.0.04 – Fdxie Jan 21 '16 at 19:36
  • If the subquery is returning more than one row from table_with_update_info for a given pkg_type, then it's up to you to figure out what the rules are to select the correct row to use. You will need to look at the data and work out your business rules before we can give you a correct answer in this instance. You can "SELECT shipment, count(*) from table_with_update_info group by shipment having count(*) > 1" to find the shipment rows that are causing the problem, and then dig into the data to determine how you need to proceed. – Michael Broughton Jan 22 '16 at 12:55