Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2014
    Posts
    18

    Unanswered: Subquery Structure

    I have a list of file numbers in a table and need to look up information in other tables in the same database for each of the file numbers in the list, then update the table with the query result for each file number.

    Looking for help with syntax here. I believe I’m missing a concept and not sure what it is

    I first wrote a query that returns a list of the numbers to bring in to the table, then UPDATE Table SET FileNum = Query... but I keep getting “subquery returns more than one result” and not sure how I should approach this task.

    What would the basic structure look like for a query that will update a table with subquery results for each record?

    FYI, I'm using MS SQL Server 2008.

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try giving a few examples... Based on what you've described, SQL Server is doing exactly what I would expect and I'm not sure what else you might want it to do. Once I understand what you want, I think that the answer will be simple but right now I'm clueless on how I could help you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2014
    Posts
    18
    Here's an example of the table I have:

    Code:
    FileNum     Costs 
    ---------     -------
    1               NULL
    2               NULL
    3               NULL
    4               NULL
    5               NULL
    ...              ...
    The data I need in the costs column has to come from another query with a join, etc. I've worked that part out and can get a list of the costs, but I don't know how to get that list of costs in the Cost column in my table.

    Does that help? Have learned a lot in the past few months but still have a ways to go.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So based on:
    Code:
    DECLARE @widgets TABLE  (
       widget_id    CHAR(3)         NOT NULL
    ,  name         NVARCHAR(25)    NOT NULL
    ,  cost         DECIMAL(6, 1)       NULL
    )
    
    DECLARE @dinguses TABLE (
       dingus_id    INT             NOT NULL
    ,  widget_id    CHAR(3)         NOT NULL
    ,  wombat       DECIMAL(6, 1)   NOT NULL
    )
    
    INSERT INTO @widgets (widget_id, name, cost) VALUES
       ('AAA', 'First Widget', NULL)
    ,  ('BBB', 'Second Widget', NULL)
    ,  ('CCC', 'Third Widget', NULL)
    
    INSERT INTO @dinguses (dingus_id, widget_id, wombat) VALUES
       (10, 'AAA', 8.2), (11, 'AAA', 4.3), (12, 'AAA', 1.1)
    ,  (21, 'CCC', 1.3), (22, 'CCC', 5.7)
    ...what would you expect?

    -PatP
    Last edited by Pat Phelan; 07-23-14 at 13:55.
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2014
    Posts
    18
    INSERT INTO @widgets (widget_id, name, cost) VALUES
    ('AAA', 'First Widget', NULL)
    , ('BBB', 'Second Widget', NULL)
    , ('CCC', 'Third Widget', NULL)

    INSERT INTO @dinguses (dingus_id, widget_id, wombat) VALUES
    (10, 'AAA', 8.2), (11, 'AAA', 4.3), (12, 'AAA', 1.1)
    , (21, 'CCC', 1.3), (22, 'CCC', 5.7)

    Based on your code I would expect to see:

    Code:
    @widgets:
    widget_id       name            cost
    -----------      -------           -----
    AAA              First Widet     NULL
    BBB              Second Widget   NULL
    CCC              Third Widget   NULL
    
    
    @dinguses:
    dingus_id              widget_id        wombat
    ----------               -----------        ---------
    10                         AAA                8.2
    11                         AAA                4.3
    12                         AAA                1.1
    21                         CCC                1.3
    22                         CCC                5.7
    Is that right?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by LegalEagle View Post
    Is that right?
    That's what you would get from simple SELECT statements. What I meant was: What result would you expect based on this data being used in your query"

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    since we are side stepping everything here and just talking dinguses and wombats. It sounds like what you are attempting to get across to us is that you are receiving multiple costs for the same filenum. That is where you need to concentrate your time. Run your subquery and find where you are getting the multiple records, wrap it up with a count(*) having count >1 to just see those particular rows.
    Dave

  8. #8
    Join Date
    Mar 2014
    Posts
    18
    The table contains a list of filenums (42, to be exact). When I run my subquery, I get a list of 42 rows of cost amounts... which is what I would expect. I'm trying to put this list of 42 cost amounts in the cost column.

    I think it's me.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is by no means a solution, but it might point you in a useful direction:
    Code:
    WITH s1 AS (
       SELECT number / 42 AS g, number %42 AS i, number
          FROM master.dbo.spt_values
    	  WHERE 'P' = type
    ), s2 AS (
       SELECT g, i, Cast(number AS VARCHAR(999)) AS splat
          FROM s1
    	  WHERE 0 = i
       UNION ALL SELECT s1.g, s1.i, Cast(splat + ', ' + Cast(number AS VARCHAR) AS VARCHAR(999)) AS splat
          FROM s1
    	  INNER JOIN s2
    	     ON (s2.g = s1.g
    		 AND s2.i + 1 = s1.i)
    )
    SELECT g, Max(splat)
       FROM s2
       GROUP BY s2.g
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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