inner selects to pass results back to the originating select statment

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

mphillip85Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Try it this way:

SELECT  COUNT(*)
FROM    ( SELECT    [table]
          FROM      tables
          WHERE     ACTIVE = 1
        ) AS t1
WHERE   mycolumn = 'UPDATE' ;
 
0
Eugene ZCommented:
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
mphillip85Author Commented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Anthony PerkinsCommented:
>>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
mphillip85Author Commented:
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
Anthony PerkinsCommented:
Try it like this:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mphillip85Author Commented:
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
Anthony PerkinsCommented:
if you cannot post the structure of your table(s) then I am afraid I cannot help you any further.

Good luck.
0
mphillip85Author Commented:
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
mphillip85Author Commented:
since I had to do a group by since there was a count(*) involved.
0
Anthony PerkinsCommented:
>>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
mphillip85Author Commented:
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
Anthony PerkinsCommented:
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
Anthony PerkinsCommented:
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
mphillip85Author Commented:
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
Anthony PerkinsCommented:
>>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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.