December 28, 2016 at 11:59 am
I need to choose multiple misc. accounts from an account table using a list from one of our accountants and then change to make account active. I am a sql beginner at best....I.E. I have 200 account number out of 1000 that need to be changed and they are in random number order...
Is there a best method to use to create the SQL query other than using Select * from <TABLENAME> where ACCT = 111 or 112 or 555 or 654 or....etc etc?
Any help would be greatly appreciated!
Thanks
December 28, 2016 at 12:06 pm
In (111, 112, 555 etc) would be better.
Although if there's a lot you might want to have a parent attribute or even a separate table
- Damian
December 28, 2016 at 12:41 pm
Actually ACCT IN (111, 222, 555, etc.) is shorthand for ACCT = 111 OR ACCT = 222 OR ACCT = 555, etc., so they are equivalent in terms of performance.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 28, 2016 at 12:48 pm
jason.shaw (12/28/2016)
I need to choose multiple misc. accounts from an account table using a list from one of our accountants and then change to make account active. I am a sql beginner at best....I.E. I have 200 account number out of 1000 that need to be changed and they are in random number order...Is there a best method to use to create the SQL query other than using Select * from <TABLENAME> where ACCT = 111 or 112 or 555 or 654 or....etc etc?
Any help would be greatly appreciated!
Thanks
Insert all the account numbers that you need into a table and use the table as a filter. You could either use an INNER JOIN, IN or EXISTS. For examples on that, check this article: http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/ and the one linked at the end.
Also, if you want to change the values, you need to use UPDATE instead of SELECT.
If you just want to get 200 random rows use NEWID() to order the values.
SELECT TOP (200) *
FROM YourTable
ORDER BY NEWID();
December 28, 2016 at 1:23 pm
SELECT *
FROM <TABLENAME>
WHERE ACCOUNT_NUMBER IN ('100', '150', '175')
and once those acct numbers are selected per query above, I need to change an field value for each of them to make them inactive...change a 1 to 0
what would the next line of code need to be?
THanks !
December 28, 2016 at 2:10 pm
jason.shaw (12/28/2016)
SELECT *FROM <TABLENAME>
WHERE ACCOUNT_NUMBER IN ('100', '150', '175')
and once those acct numbers are selected per query above, I need to change an field value for each of them to make them inactive...change a 1 to 0
what would the next line of code need to be?
THanks !
Update <TableName> Set
ActiveStatus = 0
WHERE ACCOUNT_NUMBER IN ('100', '150', '175')
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 30, 2016 at 8:21 am
Luis Cazares (12/28/2016)
jason.shaw (12/28/2016)
I need to choose multiple misc. accounts from an account table using a list from one of our accountants and then change to make account active. I am a sql beginner at best....I.E. I have 200 account number out of 1000 that need to be changed and they are in random number order...Is there a best method to use to create the SQL query other than using Select * from <TABLENAME> where ACCT = 111 or 112 or 555 or 654 or....etc etc?
Any help would be greatly appreciated!
Thanks
Insert all the account numbers that you need into a table and use the table as a filter. You could either use an INNER JOIN, IN or EXISTS.
[/code]
I second Luis. Add your random Ids to a table and join or use exists. Let the join filter for you. Much more efficient.
Thank you,
Yell McGuyer
DataYell.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply