Results 1 to 8 of 8
  1. #1
    Join Date
    May 2010
    Posts
    4

    Unanswered: Update SQL Query with Multi-Part Where Clause

    Hello,

    I am semi new to SQL server administration. I am trying to run an update on one of my databases. Here is the Query I am trying to run.

    Update PartsLabor Set
    PartsLabor.Units = (Select Units from Table_1)
    Where Partnum =(Select PartNum from Table_1 where PartsLabor.PartNum = Table_1.PartNum)
    and LaborType =(Select LaborType from Table_1 where PartsLabor.LaborType = Table_1.LaborType)


    I am getting the following error when I run this query

    Msg 512, Level 16, State 1, Line 1
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.

    I can't use "in" instead of "=" because there are a lot of records that may have either the PartNum or the LaborType but I need it to match on both records to update the specific row.

    Thanks in advance,
    Carrie

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Don't forget to back up and test your stuff first
    Code:
    UPDATE PartsLabor
    SET    Units = Table_1.Units
    FROM   PartsLabor
     INNER
      JOIN Table_1
        ON Table_1.PartNum = PartsLabor.PartNum
       AND Table_1.LaborType = PartsLabor.LaborType
    George
    Home | Blog

  3. #3
    Join Date
    May 2010
    Posts
    4
    Thanks George! I did make a backup and am actually not using the production database to test.

    I tried this query and I am getting an error.

    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'INNER'.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Access is a b*tch for SQL.
    Try swapping the table names in the ON clause (PartsLabour.PartNum = Table_1.PartNum... etc.)

  5. #5
    Join Date
    May 2010
    Posts
    4
    Thanks Pootle Flump!

    I tried, no such luck! Same error as before...

    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'INNER'.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    poots, this is sql server, not access

    Code:
    UPDATE PartsLabor
       SET Units = two.Units
      FROM PartsLabor AS one
    INNER
      JOIN Table_1 AS two
        ON two.PartNum = one.PartNum
       AND two.LaborType = one.LaborType
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937 View Post
    poots, this is sql server, not access
    Whoops - need to pay more attention...

  8. #8
    Join Date
    May 2010
    Posts
    4
    Thank you, Thank you, Thank you! Worked like a charm!!!

    Thanks for all of the help!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •