6 Replies - 1113 Views - Last Post: 29 November 2011 - 03:39 PM Rate Topic: -----

#1 Dev1462  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 145
  • Joined: 20-May 10

Bad syntax for my SQL update statement

Posted 29 November 2011 - 03:05 PM

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?

Is This A Good Question/Topic? 0
  • +

Replies To: Bad syntax for my SQL update statement

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4332
  • View blog
  • Posts: 12,127
  • Joined: 18-April 07

Re: Bad syntax for my SQL update statement

Posted 29 November 2011 - 03:09 PM

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

Was This Post Helpful? 1
  • +
  • -

#3 Dev1462  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 145
  • Joined: 20-May 10

Re: Bad syntax for my SQL update statement

Posted 29 November 2011 - 03:26 PM

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
Was This Post Helpful? 0
  • +
  • -

#4 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Bad syntax for my SQL update statement

Posted 29 November 2011 - 03:28 PM

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

Was This Post Helpful? 2
  • +
  • -

#5 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4332
  • View blog
  • Posts: 12,127
  • Joined: 18-April 07

Re: Bad syntax for my SQL update statement

Posted 29 November 2011 - 03:35 PM

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.
Was This Post Helpful? 1
  • +
  • -

#6 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Bad syntax for my SQL update statement

Posted 29 November 2011 - 03:37 PM

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

Was This Post Helpful? 1
  • +
  • -

#7 Dev1462  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 145
  • Joined: 20-May 10

Re: Bad syntax for my SQL update statement

Posted 29 November 2011 - 03:39 PM

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 >.<
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1