How can I correct my SQL syntax to allow for a subquery?

Posted on 2008-11-07
Last Modified: 2012-05-05

I need help adding with my SQL syntax.  The problem centers around a query within a query.
INSERT INTO table1(val1, val2, val3) select val1,(SELECT TOP 1 * from table2 ORDER BY val1 DESC) + 1, 1 from table3'

Open in new window

Question by:trumpman
    LVL 92

    Accepted Solution

    Looks like you want the minimum from a column...

    INSERT INTO table1 (val1, val2, val3)
    SELECT val1,
        (SELECT MIN(t2.val1) FROM table2 t2) + 1,
    FROM table3
    LVL 51

    Assisted Solution

    by:Mark Wills
    you cannot select multiple columns in the subquery, so, need to understand what you are trying to retrieve from table2

    assuming it is val1 from table2, then matthewspatrick suggestion is a good one.  using your original syntax, just swap the * for the name of the column. which works well when there is a different column to order by from the value you are trying to return (e.g. like a date being "most recent" or something), otherwise use the aggregate functions as suggested by matthewspatrick - but should have been MAX...

    e.g.1 original syntax:

    INSERT INTO table1(val1, val2, val3) select val1,(SELECT TOP 1 val1 from table2 ORDER BY val1 DESC) + 1, 1 from table3

    e.g.2 matthews updated syntax:

    INSERT INTO table1 (val1, val2, val3)  SELECT val1,  (SELECT MAX(val1) FROM table2) + 1, 1 FROM table3

    and might also be worthwhile checking for NULL just for completeness...


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now