Solved

inner selects to pass results back to the originating select statment

Posted on 2009-07-01
16
414 Views
Last Modified: 2012-06-21
I am trying to get a list of tables from a table that stores table names with a where clause that gives me only the tables i need. I have a select for that, but I need to pass the tables to the beginning select statement that needs to take the tablename passed and do a count or where clause to get the data I need.
select count(*) from (select table from tables where ACTIVE = 1) AS t1) where mycolumn = 'UPDATE';
 
I am trying to pass the tables so I can get a count and tablename and update mycolumn to 'UPDATED'
 
I want to select the data first to verify contents before I do an update on the records.

Open in new window

0
Comment
Question by:mphillip85
  • 8
  • 7
16 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24759508
Try it this way:

SELECT  COUNT(*)
FROM    ( SELECT    [table]
          FROM      tables
          WHERE     ACTIVE = 1
        ) AS t1
WHERE   mycolumn = 'UPDATE' ;
 
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24759934
mphillip85:
it  needs your additional clarification:
<I am trying to pass the tables  so I can get a count and tablename and update mycolumn to 'UPDATED'
<I want to select the data first to verify contents before I do an update on the records.>

-----------------------------------------------------------------------------------------------------

check if it can  help ...

select table, count(*)  from tables where ACTIVE = 1  and  mycolumn = 'UPDATE';

update tables
set  mycolumn ='UPDATED'
where  ACTIVE = 1  and  mycolumn = 'UPDATE';
 

...
0
 

Author Comment

by:mphillip85
ID: 24764472
Invalid column name 'mycolumn'.

No matter which example I use it comes back with the error message above.

(select mytable from mytables where mycolumn = 'ACTIVE')
This will give me the list of tables.  

I believe I need a fetch cursor routine.  But I do not want to use one, if I do not have to.
If you can write me a fetch that would accomplish this.  That would help as well.


0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24765331
>>Invalid column name 'mycolumn'.<<
You do realize that is the name of the column as you posted it, right?

Since it is obviously a fake name as is "table" why don't you post the structure of the table and then we can give you a valid query for something that is trivial to accomplish, provided you have the right column/table names, that is.
0
 

Author Comment

by:mphillip85
ID: 24765669
SELECT  COUNT(*)
FROM    ( SELECT    dbtable
          FROM      dbobject
          WHERE     dbcolumn = 'priceindex'
        )
WHERE   priceindex = 'myprice' ;

The inner select have the table names to pull from and each of the tables in the inner select have a column called priceindex
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24766373
Try it like this:

SELECT  COUNT(*)
FROM    ( SELECT    dbtable
          FROM      dbobject
          WHERE     dbcolumn = 'priceindex'
        ) d
WHERE   priceindex = 'myprice' ;
0
 

Author Comment

by:mphillip85
ID: 24767311
Invalid column name 'priceindex'.

The inner select statement pulls tables that have a column named priceindex

The outer select is not grabbing the table name passed and then checking the priceindex column to see if the where statement matches

the outer select does not pull columns from the table being passed, its still referecing what columns I  pull from the inner select statement
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24769014
if you cannot post the structure of your table(s) then I am afraid I cannot help you any further.

Good luck.
0
 

Author Comment

by:mphillip85
ID: 24775440
so you are telling me that you do not know why the outer select statement is not retrieving values being passed by the inner select statement?  You said this was simple, in your earlier comment, I guess it is not as simple as you stated.  

I will wait for another person or moderator to help.  I do appreciate you trying to help me.  I then ask you to give me the syntax for a DECLARE CURSOR in place of the select statement or translate to a cursor statement.  You were very snobbish when you said this was trivial.  I dont expect that from someone who wants to help.

There is another genius that said it was not possible to do this in another question I checked.  But you are telling me based on your statements that it is possible.  But, now all I want is a cursor statement to do what I am asking or translate the select statement into a cursor statement for me if you would.  I know that works.  So will you help me with that?
0
 

Author Closing Comment

by:mphillip85
ID: 31599010
since I had to do a group by since there was a count(*) involved.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 25009453
>>since I had to do a group by since there was a count(*) involved.<<
Huh?  There is no GROUP BY required in the SQL Statement I posted:

SELECT  COUNT(*)
FROM    ( SELECT    dbtable
          FROM      dbobject
          WHERE     dbcolumn = 'priceindex'
        ) d
WHERE   priceindex = 'myprice' ;

If you needed one then perhaps you can enlighten us with your "solution".

Also, if you want help on this site then lose the attitude.
0
 

Author Comment

by:mphillip85
ID: 25009790
I merely responded with what I needed to do.  It was resolved, and I was able to do what I needed to do. So was it really necessary to respond back the way you did.  Get over yourself, life is too short, and there are others out there that are on exchange-experts that really care and are sincere, and not arrogant like yourself.  

So, dont anwer my questions.  I can always find someone else who is better at answering my questions.  If you continue this thread, you are really a piece of work.  Otherwise, drop it, grow up and move on.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 25009991
You are missing the point.  We are here to help everyone.  You have stated that my answer was not sufficient, please do your part and post your solution so that everyone in this community can learn from it.

Thank you,
Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 25009995
P.S. If you feel my comments are in any way inappropriate feel free to post a message in Community Support to have a Moderator review the whole thread.
0
 

Author Comment

by:mphillip85
ID: 25028186
my apologies, i thought i had actually made it work like I wanted exactly, but i modified it too much to be the original request.  below is the code that should work, but does not

SELECT  COUNT(*)
FROM    ( SELECT    dbtable
          FROM      dbobject
          WHERE     dbcolumn = 'priceindex'
        ) d
WHERE   d.priceindex = 'myprice' ;
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 25029317
>>below is the code that should work, but does not<<
Can you tell me what does this mean? Do you get:

A. An error message.  If so what is it.
B. The wrong results.  If so what are they and what are the desired results.
C. Something else.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 33
Download ms sql express. 2 26
changing page verifacation 1 27
SQL Find Carriage Return and Delete it. 3 11
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

856 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