[Dev1462]

Hi everyone, I'm currently stuck on a homework assignment which goes as follows:

Quote

Write an UDPDATE statement that modifies the Invoices table. Change the terms_id to 2 for each invoice that's for a vendor with a default_terms_id of 2

The default_terms_id is a column in another table called Vendors.

My second attempt at this is:

update invoices
set terms_id = 2
where vendor_id =
(select vendor_id 
from vendors
where default_terms_id = 2)

However I get get errors saying the subquery needs to only return a single value, but this is the only solution to something he posted. Do i need a join of some kind?

[Martyr2]

Wouldn't the update work if you do an actual join?

update invoices set terms_id = 2 from invoices, vendors where invoices.vendor_id = vendors.vendor_id and vendors.default_terms_id = 2

Give that a whirl. This should link the two tables and only update those invoices where they have a vendor that has default_terms_id = 2.

This post has been edited by Martyr2: 29 November 2011 - 03:13 PM

[Dev1462]

Ahh that seemed like it would work! But no i got this error

Quote

Error starting at line 1 in command:
update invoices
set terms_id = 2
from invoices, vendors
where invoices.vendor_id = vendors.vendor_id
and vendors.default_terms_id = 2
Error at Command Line:2 Column:16
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:

I didn't think you could use a from in an update clause

[Ionut]

What Martyr2 said should work, but to overcome the error you have to replace equal operator with in

update invoices
set terms_id = 2
where vendor_id in
(select vendor_id 
from vendors
where default_terms_id = 2)

"=" expects only one value (the equality is evaluated between two identical objects), but in vendor table you may have more than 1 vendor with the required property. Now you try to evaluate an integer and a result set(like another table with one column). "In" is used to check if a particular value is among the values from a subquery.

This post has been edited by Ionut: 29 November 2011 - 03:28 PM

[Martyr2]

Apparently Oracle doesn't support this method. More information on a solution can be found here..

http://www.dba-oracl...mmand_ended.htm

There is also an example query showing you an alternative example. I believe you can run joins in an update query in other databases. I am constantly surprised at Oracle.

[Ionut]

Ok, so you use Oracle. The syntax Martyr posted is for MSSQL and doesn't work for Oracle.
Now, change the query you have using this idea or use the query I posted. Choose one and if it works post your solution here for posterity.


EDIT: Today I'm delayed...can't make these fingers type faster.

This post has been edited by Ionut: 29 November 2011 - 03:39 PM

[Dev1462]

Ionut you are right on the money, thank you man. I was struggling with this for the passed 40 minutes...its always the small stuff