Professional Web Applications Themes

Need Urgent assistance with a SQL Query - Microsoft SQL / MS SQL Server

I would really really appreciate help with this issue. I have an accounting database which has tables which have the cpnyid field and the subaccount field. I am trying to update the cpnyid field in the tables based on the first character of the subaccount. For e.g Where the subaccount is S-30-0-LAYUP I would like to populate all the tables with the value SARC in the CPNYID field, where the Subaccount is L-65-0-LAMSD I would like to populate the CPNYID field with the value TRAC in the CPNYID field. So what I need is 1. Query that will tell me ...

  1. #1

    Default Need Urgent assistance with a SQL Query

    I would really really appreciate help with this issue.

    I have an accounting database which has tables which have the cpnyid
    field and the subaccount field.

    I am trying to update the cpnyid field in the tables based on the first
    character of the subaccount.

    For e.g Where the subaccount is
    S-30-0-LAYUP I would like to populate all the tables with the value SARC
    in the CPNYID field,
    where the Subaccount is L-65-0-LAMSD I would like to populate the CPNYID
    field with the value TRAC in the CPNYID field.

    So what I need is
    1. Query that will tell me all tables that have the CPNYID and
    SUBACCOUNT coloums.

    2. Develop Query that will allow me to mass update the CPNYID field in
    all the tables based on the first character of the subaccount.

    I would again really be thankful for any help on this.

    Sam Commar



    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Sandeep Commar Guest

  2. #2

    Default Re: Need Urgent assistance with a SQL Query

    >1. Query that will tell me all tables that have the CPNYID
    >and SUBACCOUNT coloums.
    select table_name from information_schema.columns
    where column_name in('SUBACCOUNT', 'CPNYID')
    group by table_name
    having count(*) = 2
    >2. Develop Query that will allow me to mass update the
    >CPNYID field in all the tables based on the first
    >character of the subaccount.
    your query would look something like this.

    update table
    set cpnyid = left(subaccount,1) + 'ARC'
    --ie take 1st character of the column subaccount and concatenate it with
    'ARC'

    --
    -Vishal

    "Sandeep Commar" <s_commarhotmail.com> wrote in message
    news:#qCFUunRDHA.2084TK2MSFTNGP11.phx.gbl...
    > I would really really appreciate help with this issue.
    >
    > I have an accounting database which has tables which have the cpnyid
    > field and the subaccount field.
    >
    > I am trying to update the cpnyid field in the tables based on the first
    > character of the subaccount.
    >
    > For e.g Where the subaccount is
    > S-30-0-LAYUP I would like to populate all the tables with the value SARC
    > in the CPNYID field,
    > where the Subaccount is L-65-0-LAMSD I would like to populate the CPNYID
    > field with the value TRAC in the CPNYID field.
    >
    > So what I need is
    > 1. Query that will tell me all tables that have the CPNYID and
    > SUBACCOUNT coloums.
    >
    > 2. Develop Query that will allow me to mass update the CPNYID field in
    > all the tables based on the first character of the subaccount.
    >
    > I would again really be thankful for any help on this.
    >
    > Sam Commar
    >
    >
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Vishal Parkar Guest

  3. #3

    Default Re: Need Urgent assistance with a SQL Query


    Thanks so much for your assistance. I really appreicate it,

    Could I bother you a little more. I developed this script to create the
    scripts automatically for all tables that could get updated.

    I am getting an error on it. Could you guide me as to why its not
    working. Here is the script and the error.


    select 'update ' + table_schema + '.' + table_name + 'set cpnyid = case
    sub when left(sub,1)= ''S'' then ''S00'' when left(sub,1)= ''L'' then
    ''L00'' end' +
    char(13) + char(10)
    + 'go' + char(13) +
    char(10) from information_schema.columns
    where column_name in('SUB', 'CPNYID')
    group by table_name
    having count(*) = 2;

    The error I am getting is
    Column 'information_schema.columns.TABLE_SCHEMA' is invalid in the
    select list because it is not contained in either an aggregate function
    or the GROUP BY clause.

    Could you please assist.

    Thanks again.
    Sandeep



    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Sandeep Commar Guest

  4. #4

    Default Re: Need Urgent assistance with a SQL Query

    Can I use an if then statement to get the update for all
    possible cases

    That is rather than doing
    update table set cpnyid = left(subaccount,1) + 'ARC';

    Can I do a case statement for all possible scenarios,


    Something like
    Update table (if left(subaccount,1)= L then L00,
    else if left subaccount,1) S then S00, else if left
    subaccount,1) J then J00
    else R00;

    Thanks
    Sandeep


    >-----Original Message-----
    >>1. Query that will tell me all tables that have the
    CPNYID
    >>and SUBACCOUNT coloums.
    >
    >select table_name from information_schema.columns
    >where column_name in('SUBACCOUNT', 'CPNYID')
    >group by table_name
    >having count(*) = 2
    >
    >>2. Develop Query that will allow me to mass update the
    >>CPNYID field in all the tables based on the first
    >>character of the subaccount.
    >
    >your query would look something like this.
    >
    >update table
    >set cpnyid = left(subaccount,1) + 'ARC'
    >--ie take 1st character of the column subaccount and
    concatenate it with
    >'ARC'
    >
    >--
    >-Vishal
    >
    >"Sandeep Commar" <s_commarhotmail.com> wrote in message
    >news:#qCFUunRDHA.2084TK2MSFTNGP11.phx.gbl...
    >> I would really really appreciate help with this issue.
    >>
    >> I have an accounting database which has tables which
    have the cpnyid
    >> field and the subaccount field.
    >>
    >> I am trying to update the cpnyid field in the tables
    based on the first
    >> character of the subaccount.
    >>
    >> For e.g Where the subaccount is
    >> S-30-0-LAYUP I would like to populate all the tables
    with the value SARC
    >> in the CPNYID field,
    >> where the Subaccount is L-65-0-LAMSD I would like to
    populate the CPNYID
    >> field with the value TRAC in the CPNYID field.
    >>
    >> So what I need is
    >> 1. Query that will tell me all tables that have the
    CPNYID and
    >> SUBACCOUNT coloums.
    >>
    >> 2. Develop Query that will allow me to mass update the
    CPNYID field in
    >> all the tables based on the first character of the
    subaccount.
    >>
    >> I would again really be thankful for any help on this.
    >>
    >> Sam Commar
    >>
    >>
    >>
    >> *** Sent via Developersdex [url]http://www.developersdex.com[/url]
    ***
    >> Don't just participate in USENET...get rewarded for it!
    >
    >
    >.
    >
    Sandeep Commar Guest

  5. #5

    Default Re: Need Urgent assistance with a SQL Query


    Thanks so much for your assistance. I really appreicate it,

    Could I bother you a little more. I developed this script to create the
    scripts automatically for all tables that could get updated.

    I am getting an error on it. Could you guide me as to why its not
    working. Here is the script and the error.


    select 'update ' + table_schema + '.' + table_name + 'set cpnyid = case
    sub when left(sub,1)= ''S'' then ''S00'' when left(sub,1)= ''L'' then
    ''L00'' end' +
    char(13) + char(10)
    + 'go' + char(13) +
    char(10) from information_schema.columns
    where column_name in('SUB', 'CPNYID')
    group by table_name
    having count(*) = 2;

    The error I am getting is
    Column 'information_schema.columns.TABLE_SCHEMA' is invalid in the
    select list because it is not contained in either an aggregate function
    or the GROUP BY clause.

    Could you please assist.

    Thanks again.
    Sandeep



    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Sandeep Commar Guest

  6. #6

    Default Re: Need Urgent assistance with a SQL Query

    I think this should correct your problem.

    select 'update ' + table_schema + '.' + table_name + ' set cpnyid = case
    left(sub,1) when ''S'' then ''S00'' when ''L'' then
    ''L00'' end' + char(13) + char(10) + 'go' + char(13) + char(10)
    from information_schema.columns
    where column_name in('customerid', 'companyname')
    group by table_schema,table_name
    having count(*) = 2

    --
    -Vishal

    "Sandeep Commar" <s_commarhotmail.com> wrote in message
    news:OjNi#PoRDHA.940TK2MSFTNGP11.phx.gbl...
    >
    > Thanks so much for your assistance. I really appreicate it,
    >
    > Could I bother you a little more. I developed this script to create the
    > scripts automatically for all tables that could get updated.
    >
    > I am getting an error on it. Could you guide me as to why its not
    > working. Here is the script and the error.
    >
    >
    > select 'update ' + table_schema + '.' + table_name + 'set cpnyid = case
    > sub when left(sub,1)= ''S'' then ''S00'' when left(sub,1)= ''L'' then
    > ''L00'' end' +
    > char(13) + char(10)
    > + 'go' + char(13) +
    > char(10) from information_schema.columns
    > where column_name in('SUB', 'CPNYID')
    > group by table_name
    > having count(*) = 2;
    >
    > The error I am getting is
    > Column 'information_schema.columns.TABLE_SCHEMA' is invalid in the
    > select list because it is not contained in either an aggregate function
    > or the GROUP BY clause.
    >
    > Could you please assist.
    >
    > Thanks again.
    > Sandeep
    >
    >
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Vishal Parkar Guest

  7. #7

    Default Re: Need Urgent assistance with a SQL Query

    I thnk you already got this using CASE expression.

    --
    -Vishal

    "Sandeep Commar" <s_commarhotmail.com> wrote in message
    news:084901c34685$3d273440$a301280aphx.gbl...
    > Can I use an if then statement to get the update for all
    > possible cases
    >
    > That is rather than doing
    > update table set cpnyid = left(subaccount,1) + 'ARC';
    >
    > Can I do a case statement for all possible scenarios,
    >
    >
    > Something like
    > Update table (if left(subaccount,1)= L then L00,
    > else if left subaccount,1) S then S00, else if left
    > subaccount,1) J then J00
    > else R00;
    >
    > Thanks
    > Sandeep
    >
    >
    >
    > >-----Original Message-----
    > >>1. Query that will tell me all tables that have the
    > CPNYID
    > >>and SUBACCOUNT coloums.
    > >
    > >select table_name from information_schema.columns
    > >where column_name in('SUBACCOUNT', 'CPNYID')
    > >group by table_name
    > >having count(*) = 2
    > >
    > >>2. Develop Query that will allow me to mass update the
    > >>CPNYID field in all the tables based on the first
    > >>character of the subaccount.
    > >
    > >your query would look something like this.
    > >
    > >update table
    > >set cpnyid = left(subaccount,1) + 'ARC'
    > >--ie take 1st character of the column subaccount and
    > concatenate it with
    > >'ARC'
    > >
    > >--
    > >-Vishal
    > >
    > >"Sandeep Commar" <s_commarhotmail.com> wrote in message
    > >news:#qCFUunRDHA.2084TK2MSFTNGP11.phx.gbl...
    > >> I would really really appreciate help with this issue.
    > >>
    > >> I have an accounting database which has tables which
    > have the cpnyid
    > >> field and the subaccount field.
    > >>
    > >> I am trying to update the cpnyid field in the tables
    > based on the first
    > >> character of the subaccount.
    > >>
    > >> For e.g Where the subaccount is
    > >> S-30-0-LAYUP I would like to populate all the tables
    > with the value SARC
    > >> in the CPNYID field,
    > >> where the Subaccount is L-65-0-LAMSD I would like to
    > populate the CPNYID
    > >> field with the value TRAC in the CPNYID field.
    > >>
    > >> So what I need is
    > >> 1. Query that will tell me all tables that have the
    > CPNYID and
    > >> SUBACCOUNT coloums.
    > >>
    > >> 2. Develop Query that will allow me to mass update the
    > CPNYID field in
    > >> all the tables based on the first character of the
    > subaccount.
    > >>
    > >> I would again really be thankful for any help on this.
    > >>
    > >> Sam Commar
    > >>
    > >>
    > >>
    > >> *** Sent via Developersdex [url]http://www.developersdex.com[/url]
    > ***
    > >> Don't just participate in USENET...get rewarded for it!
    > >
    > >
    > >.
    > >

    Vishal Parkar Guest

  8. #8

    Default Re: Need Urgent assistance with a SQL Query

    > Update apdoc if left(subaccount,1)= 'L' then 'L00',
    > else if left(subaccount,1)= 'S' then 'S00', else if left
    > (subaccount,1)= 'J' then 'J00'
    > else 'R00';
    I don't know what language you think you're coding in; there shouldn't be
    any commas in there, and if/else if/else are not valid in a query, they are
    for program control. :-)

    I'm also not clear on which column you're trying to update. However this
    might help:

    UPDATE apdoc SET columnname = CASE
    WHEN LEFT(subaccount, 1) IN ('J', 'L', 'S') THEN
    LEFT(subaccount, 1)
    ELSE
    'R'
    END + '00'


    Aaron Bertrand [MVP] Guest

  9. #9

    Default Re: Need Urgent assistance with a SQL Query


    Update <table_name> set <column> = case left(subaccount,1) when 'L' then
    'L00'
    when 'S' then 'S00' when 'J' then 'J00' else 'R00' end

    Above condition is equivalent of if-elseif-else-endif condition in sql
    server that you can accomplish using SQL query.

    --
    -Vishal
    Sandeep <s_commarhotmail.com> wrote in message
    news:097f01c34689$a1ce94c0$a101280aphx.gbl...
    > Vishal If i use the following statemetn.
    >
    > Update apdoc if left(subaccount,1)= 'L' then 'L00',
    > else if left(subaccount,1)= 'S' then 'S00', else if left
    > (subaccount,1)= 'J' then 'J00'
    > else 'R00';
    >
    > I get errors as below
    > Server: Msg 156, Level 15, State 1, Line 1
    > Incorrect syntax near the keyword 'if'.
    > Server: Msg 156, Level 15, State 1, Line 1
    > Incorrect syntax near the keyword 'then'.
    > Server: Msg 156, Level 15, State 1, Line 2
    > Incorrect syntax near the keyword 'then'.
    > Server: Msg 156, Level 15, State 1, Line 3
    > Incorrect syntax near the keyword 'then'.
    >
    >
    > Please help
    >
    > Thanks
    > Sandeep
    >
    >
    >
    > >-----Original Message-----
    > >I thnk you already got this using CASE expression.
    > >
    > >--
    > >-Vishal
    > >
    > >"Sandeep Commar" <s_commarhotmail.com> wrote in message
    > >news:084901c34685$3d273440$a301280aphx.gbl...
    > >> Can I use an if then statement to get the update for all
    > >> possible cases
    > >>
    > >> That is rather than doing
    > >> update table set cpnyid = left(subaccount,1) + 'ARC';
    > >>
    > >> Can I do a case statement for all possible scenarios,
    > >>
    > >>
    > >> Something like
    > >> Update table (if left(subaccount,1)= L then L00,
    > >> else if left subaccount,1) S then S00, else if left
    > >> subaccount,1) J then J00
    > >> else R00;
    > >>
    > >> Thanks
    > >> Sandeep
    > >>
    > >>
    > >>
    > >> >-----Original Message-----
    > >> >>1. Query that will tell me all tables that have the
    > >> CPNYID
    > >> >>and SUBACCOUNT coloums.
    > >> >
    > >> >select table_name from information_schema.columns
    > >> >where column_name in('SUBACCOUNT', 'CPNYID')
    > >> >group by table_name
    > >> >having count(*) = 2
    > >> >
    > >> >>2. Develop Query that will allow me to mass update the
    > >> >>CPNYID field in all the tables based on the first
    > >> >>character of the subaccount.
    > >> >
    > >> >your query would look something like this.
    > >> >
    > >> >update table
    > >> >set cpnyid = left(subaccount,1) + 'ARC'
    > >> >--ie take 1st character of the column subaccount and
    > >> concatenate it with
    > >> >'ARC'
    > >> >
    > >> >--
    > >> >-Vishal
    > >> >
    > >> >"Sandeep Commar" <s_commarhotmail.com> wrote in
    > message
    > >> >news:#qCFUunRDHA.2084TK2MSFTNGP11.phx.gbl...
    > >> >> I would really really appreciate help with this
    > issue.
    > >> >>
    > >> >> I have an accounting database which has tables which
    > >> have the cpnyid
    > >> >> field and the subaccount field.
    > >> >>
    > >> >> I am trying to update the cpnyid field in the tables
    > >> based on the first
    > >> >> character of the subaccount.
    > >> >>
    > >> >> For e.g Where the subaccount is
    > >> >> S-30-0-LAYUP I would like to populate all the tables
    > >> with the value SARC
    > >> >> in the CPNYID field,
    > >> >> where the Subaccount is L-65-0-LAMSD I would like to
    > >> populate the CPNYID
    > >> >> field with the value TRAC in the CPNYID field.
    > >> >>
    > >> >> So what I need is
    > >> >> 1. Query that will tell me all tables that have the
    > >> CPNYID and
    > >> >> SUBACCOUNT coloums.
    > >> >>
    > >> >> 2. Develop Query that will allow me to mass update
    > the
    > >> CPNYID field in
    > >> >> all the tables based on the first character of the
    > >> subaccount.
    > >> >>
    > >> >> I would again really be thankful for any help on
    > this.
    > >> >>
    > >> >> Sam Commar
    > >> >>
    > >> >>
    > >> >>
    > >> >> *** Sent via Developersdex
    > [url]http://www.developersdex.com[/url]
    > >> ***
    > >> >> Don't just participate in USENET...get rewarded for
    > it!
    > >> >
    > >> >
    > >> >.
    > >> >
    > >
    > >
    > >.
    > >

    Vishal Parkar Guest

  10. #10

    Default Re: Need Urgent assistance with a SQL Query



    Sorry to bother you but there is another element that has cropped up and
    this is that we also have to check the 5th character in the subaccount
    to see what CPNYID should be used. We have to see if the 5th character
    is a 'C'

    EG if subaccount is L000012345 then we make CPNYID = 'LOO'

    however if subaccount is LOOOC12345 then we make the CPNYID = 'LOC'.

    Is there any way that we can check the first and the fifth character of
    the substring to make the the changes to CPNYID field.

    Thanks again for everything

    Sam COmmar

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Sandeep Guest

  11. #11

    Default Need Urgent assistance with a SQL Query

    update tbl
    set CPNYID = case when left(subaccount,1) ='L' and
    substring(subaccount,5,1) = 'C' then 'LOC' else 'LOO' end
     
    the 5th element 
    subaccount to see 
    character is a 'C' 
    CPNYID = 'LOC'. 
    fifth character of 
    *** 
    Nigel Guest

Similar Threads

  1. I could use some assistance...
    By atech in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: September 20th, 04:34 PM
  2. Need some assistance please
    By AcidFX in forum Macromedia Contribute Connection Administrtion
    Replies: 0
    Last Post: April 13th, 08:14 PM
  3. Connection Issues - Need Urgent Assistance!!!
    By kwilliams in forum Macromedia Contribute Connection Administrtion
    Replies: 1
    Last Post: April 6th, 06:56 PM
  4. please urgent assistance needed.
    By mariam seseseko in forum Ruby
    Replies: 0
    Last Post: September 29th, 09:17 AM
  5. SQL Query Assistance
    By Sam Commar in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 10th, 12:37 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139