0

I'm trying to update multiple rows in a SQL Server table in VB.NET code. However, I am focusing on updating one row before I include more SELECT statements. I understand the following method is very flexible, but it is throwing the error "SQL command not properly ended":

UPDATE ndcs 
SET    PACKAGE_TYPE_CODE = x.package_type_code, 
       PACKAGE_DESC = x.package_desc, 
       COMMENTS = x.comments 
FROM   hub_non_dcs_product_pkgtype ndcs 
       INNER JOIN (SELECT PRODUCT_ID = 'SEN0982_pdf_1', 
                          PACKAGE_TYPE_CODE = '', 
                          PACKAGE_DESC = '', 
                          COMMENTS = 'DPDC LITERATURE') x 
               ON x.product_id = ndcs.product_id

What is wrong with my syntax?

LiborStefek
  • 400
  • 4
  • 16
  • To be honest I can't see anything wrong with that - I ran it through SQL 2008 and it worked fine. Have you tried what I call the 'just in case stupid factor' changes, like `'SEN0982_pdf_1' AS PRODUCT_ID...`. Or even, strip it right back and try with a `WHERE` clause from the off? – Paul Jan 31 '13 at 18:42
  • Do you have the full error message to post? I'm not finding any immediate syntax errors either. – Matt Jan 31 '13 at 19:08
  • 1
    Your error message appears to be an Oracle error, are you sure this is SQL Server? What happens if you put a semi-colon at the end of your query? Can you show the VB.NET code that executes the SQL code? And as others have said, the full error message/stack trace would be useful. – Pondlife Jan 31 '13 at 19:28
  • As @Pondlife suggests, are you sure this query is run against SQL-Server and not Oracle? – ypercubeᵀᴹ Jan 31 '13 at 19:36
  • 1
    Assuming it is Oracle, it's probably [this issue](http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join) – Pondlife Jan 31 '13 at 19:42
  • I've been bouncing between Oracle and SQL Server all day. This is Oracle SQL. Sorry for the confusion. –  Jan 31 '13 at 19:44

1 Answers1

1

Try this:

UPDATE ndcs 
SET (PACKAGE_TYPE_CODE, PACKAGE_DESC, COMMENTS )  = (
SELECT
    x.package_type_code, 
    x.package_desc, 
    x.comments 
FROM   
    hub_non_dcs_product_pkgtype ndcs 
    INNER JOIN (SELECT PRODUCT_ID = 'SEN0982_pdf_1', 
                      PACKAGE_TYPE_CODE = '', 
                      PACKAGE_DESC = '', 
                      COMMENTS = 'DPDC LITERATURE') x 
           ON x.product_id = ndcs.product_id 
)
woemler
  • 7,089
  • 7
  • 48
  • 67