Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] SQL update help

Expand Messages
  • John Warner
    Actually it should have been the _ underscore. I only wanted to match a single character. The % character matches zero or more characters. But you are
    Message 1 of 22 , Oct 17, 2009
    • 0 Attachment
      Actually it should have been the "_" underscore. I only wanted to match a
      single character. The '%' character matches zero or more characters. But
      you are right, the question mark was wrong.

      John Warner


      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
      > peterim5659@...
      > Sent: Friday, October 16, 2009 1:49 PM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: Re: [SQLQueriesNoCode] SQL update help
      >
      > Wild card should be % instead of ? for MSSQL.
      > Sent from my Verizon Wireless BlackBerry
      >
      > -----Original Message-----
      > From: "John Warner" <john@...>
      > Date: Fri, 16 Oct 2009 13:20:19
      > To: <SQLQueriesNoCode@yahoogroups.com>
      > Subject: RE: [SQLQueriesNoCode] SQL update help
      >
      > UPDATE Table T
      > SET T.Description = 'Tool A'
      > WHERE Item LIKE '1234?';
      >
      > John Warner
      >
      > > -----Original Message-----
      > > From: SQLQueriesNoCode@yahoogroups.com
      > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of J P
      > > Sent: Friday, October 16, 2009 12:10 PM
      > > To: SQLQueriesNoCode@yahoogroups.com
      > > Subject: [SQLQueriesNoCode] SQL update help
      > >
      > > Hello,
      > >
      > > I have a table with item and description like below
      > >
      > > Item Description
      > > 1234a Tool A
      > > 1234b Null
      > > 1234c Null
      > > 2345a Item B
      > > 2345b Null
      > > 2345c Null
      > >
      > > How do I intra-table update the description (where null) to be the
      same
      > as the
      > > prefix item where description is not null, as below?
      > >
      > > 1234a Tool A
      > > 1234b Tool A
      > > 1234c Tool A
      > > 2345a Item B
      > > 2345b Item B
      > > 2345c Item B
      > >
      > > Thanks for any help, and yes i've created a backup copy of the table,
      so
      > any
      > > ideas will be fine to test with.
      > >
      > >
      > >
      > >
      > > [Non-text portions of this message have been removed]
      > >
      > >
      > >
      > > ------------------------------------
      > >
      > > Yahoo! Groups Links
      > >
      > >
      > >
      >
      >
      >
      >
      > [Non-text portions of this message have been removed]
      >
      >
      >
      > ------------------------------------
      >
      > Yahoo! Groups Links
      >
      >
      >
    • Muhammad Rafizeldi
      my query become slow because i used non postgresql standard function dateadd() is mssql function, i didn t realize it before to solve my problem, i use +/-
      Message 2 of 22 , Nov 9, 2009
      • 0 Attachment
        my query become slow because i used non postgresql standard function
        dateadd() is mssql function, i didn't realize it before
        to solve my problem, i use +/- operators and interval
        dateadd('year', 1, '2008/1/1') ==> date '2008/1/1' + interval '1 year'
        between dateadd('day', 1, dateadd('year', 1, '2008/1/1')) and
        dateadd('year', 2, '2008/1/1')
        ==> between date '2008/1/1' + interval '1 year' + interval '1 day' and
        date '2008/1/1' + interval '2 year'

        On Wed, Oct 14, 2009 at 8:38 AM, Aal-Rafizeldi <rafizeldi@...> wrote:

        >
        >
        > Thank you Sir, for remind me, i totally forgot about it
        > this my query
        > http://docs.google.com/View?id=df9tz3kc_49hk4vmgd6
        > <http://docs.google.com/View?id=df9tz3kc_49hk4vmgd6>
        >
        > this is my query plan
        > http://docs.google.com/View?id=df9tz3kc_47hkpcjwfr
        >
        > this is the related schema from my query
        > http://docs.google.com/View?id=df9tz3kc_46g8pcsqdn
        >
        > any help/suggestion is really appreciated...
        >
        >
        > --- In SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>,
        > "John Warner" <john@...> wrote:
        > >
        > > Without knowing the schema you ask a lot of us.
        > >
        > > John Warner
        > >
        > >
        > > > -----Original Message-----
        > > > From: SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>
        > > > [mailto:SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>]
        > On Behalf Of Aal-Rafizeldi
        > > > Sent: Tuesday, October 13, 2009 5:49 AM
        > > > To: SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>
        > > > Subject: [SQLQueriesNoCode] Help-Optimize Query
        > > >
        > > > Dear all
        > > > I am really new to complex sql query
        > > > I need help to find another possibilities for this query
        > > > there's so many subquery to skala_rental table
        > > > Ho to decrease it? since skala_rental is the biggest table, more
        > than 2
        > > > million rows
        > > > Is there any better query for this one?
        > > >
        > > > this is the query plan result queryplan.txt
        > > > <http://f1.grp.yahoofs.com/v1/kEHUStgtfgm_jmD6DOY2DK-
        > > > gZztvKuqq3gKSs7Gr5B\
        > > > yQJpJ2HI7ZZB8VGFSAqXKYl4WJ4qPecUVmYeA2uum9jN46km6RSlo/queryp
        > > > lan-mri>
        > > >
        > > > select
        > > > cf.cf_application_id as Id490_0_,
        > > > '' as CfApplic2_490_0_,
        > > > cf.cf_drawdown_number as Drawdown3_490_0_,
        > > > '' as OldDrawd4_490_0_,
        > > > p.full_name as Personal5_490_0_,
        > > > c.name as Corporat6_490_0_,
        > > > coalesce( (select name
        > > > from lookup
        > > > where lookup_id = pe.employment_status_id), 'NON GROUP'
        > > > ) as Group490_0_,
        > > >
        > > > --principle +1 tahun, 2009/1/1
        > > > coalesce( (select sum(principle)
        > > > from skala_rental
        > > > where cf_application_id = cf.cf_application_id
        > > > and (payment_date is null or payment_date >
        > '2008/1/1')
        > > > and counter <> 0
        > > > and due_date <= dateadd('year', 1, '2008/1/1')), 0
        > > > ) as Principle8_490_0_,
        > > > --interest +1 tahun, 2009/1/1
        > > > coalesce( (select sum(interest)
        > > > from skala_rental
        > > > where cf_application_id = cf.cf_application_id
        > > > and (payment_date is null or payment_date >
        > '2008/1/1')
        > > > and counter <> 0
        > > > and due_date <= dateadd('year', 1, '2008/1/1')), 0
        > > > ) as Interest12_490_0_,
        > > >
        > > > --principle (2009/1/1 + 1hari) - 2010/1/1
        > > > coalesce( (select sum(principle)
        > > > from skala_rental
        > > > where cf_application_id = cf.cf_application_id
        > > > and due_date between dateadd('day', 1, dateadd('year',
        > 1,
        > > > '2008/1/1'))
        > > > and dateadd('year', 2, '2008/1/1')), 0
        > > > ) as Principle9_490_0_,
        > > > --interest (2009/1/1 + 1hari) - 2010/1/1
        > > > coalesce( (select sum(interest)
        > > > from skala_rental
        > > > where cf_application_id = cf.cf_application_id
        > > > and due_date between dateadd('day', 1, dateadd('year',
        > 1,
        > > > '2008/1/1'))
        > > > and dateadd('year', 2, '2008/1/1')), 0
        > > > ) as Interest13_490_0_,
        > > >
        > > > --principle (2010/1/1 + 1hari) - 2011/1/1
        > > > coalesce( (select sum(principle)
        > > > from skala_rental
        > > > where cf_application_id = cf.cf_application_id
        > > > and due_date between dateadd('day', 1, dateadd('year',
        > 2,
        > > > '2008/1/1'))
        > > > and dateadd('year', 3, '2008/1/1')), 0
        > > > ) as Principle10_490_0_,
        > > > --interest (2010/1/1 + 1hari) - 2011/1/1
        > > > coalesce( (select sum(interest)
        > > > from skala_rental
        > > > where cf_application_id = cf.cf_application_id
        > > > and due_date between dateadd('day', 1, dateadd('year',
        > 2,
        > > > '2008/1/1'))
        > > > and dateadd('year', 3, '2008/1/1')), 0
        > > > ) as Interest14_490_0_,
        > > >
        > > > --principle > (2008 + 3)
        > > > coalesce( (select sum(principle)
        > > > from skala_rental
        > > > where cf_application_id = cf.cf_application_id
        > > > and due_date > dateadd('year', 3, '2008/1/1')), 0
        > > > ) as Principle11_490_0_,
        > > > --interest > (2008 + 3)
        > > > coalesce( (select sum(interest)
        > > > from skala_rental
        > > > where cf_application_id = cf.cf_application_id
        > > > and due_date > dateadd('year', 3, '2008/1/1')), 0
        > > > ) as Interest15_490_0_,
        > > >
        > > > coalesce( (sr.unearned + sr.interest), 0) as Unearne16_490_0_,
        > > > coalesce(sr.outstanding_balance, 0) as OsGross490_0_,
        > > > (select number_of_digit
        > > > from organization_setup
        > > > where organization_id = cf.organization_id
        > > > ) as NumberO18_490_0_
        > > >
        > > > from cf_application cf
        > > > left join personal_main p on p.personal_id = cf.personal_id
        > > > left join corporate_main c on c.corporate_id = cf.corporate_id
        > > > left join personal_employment pe on pe.personal_id =
        > p.personal_id
        > > > join skala_rental sr on sr.cf_application_id =
        > cf.cf_application_id
        > > >
        > > > where 1=1
        > > > and cf.drawdown_date <= '2008/1/1'
        > > > and (cf.terminate_date is null or cf.terminate_date > '2008/1/1')
        > > > and sr.counter = (select min(counter)
        > > > from skala_rental
        > > > where cf_application_id = cf.cf_application_id
        > > > and (payment_date is null or payment_date >
        > > > '2008/1/1')
        > > > and counter <> 0)
        > > > and cf.branch_id = 2
        > > > order by cf.cf_drawdown_number
        > > >
        > > > any idea?
        > > > thank's very much
        > > >
        > > >
        > > >
        > > > [Non-text portions of this message have been removed]
        > > >
        > > >
        > > >
        > > > ------------------------------------
        > > >
        > > > Yahoo! Groups Links
        > > >
        > > >
        > > >
        > >
        >
        > [Non-text portions of this message have been removed]
        >
        >
        >

        PR: wait... <javascript:{}> I: wait... <javascript:{}> L:
        wait...<javascript:{}> LD:
        wait... <javascript:{}> I: wait... <javascript:{}>wait... <javascript:{}> Rank:
        wait... <javascript:{}> Traffic: wait... <javascript:{}> Price:
        wait...<javascript:{}> C:
        wait... <javascript:{}>


        [Non-text portions of this message have been removed]
      • John Warner
        http://developer.postgresql.org/pgdocs/postgres/functions-datetime.html John Warner ... and
        Message 3 of 22 , Nov 10, 2009
        • 0 Attachment
          http://developer.postgresql.org/pgdocs/postgres/functions-datetime.html

          John Warner


          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Muhammad
          > Rafizeldi
          > Sent: Monday, November 09, 2009 9:41 PM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: Re: [SQLQueriesNoCode] Re: Help-Optimize Query
          >
          > my query become slow because i used non postgresql standard function
          > dateadd() is mssql function, i didn't realize it before
          > to solve my problem, i use +/- operators and interval
          > dateadd('year', 1, '2008/1/1') ==> date '2008/1/1' + interval '1 year'
          > between dateadd('day', 1, dateadd('year', 1, '2008/1/1')) and
          > dateadd('year', 2, '2008/1/1')
          > ==> between date '2008/1/1' + interval '1 year' + interval '1 day'
          and
          > date '2008/1/1' + interval '2 year'
          >
          > On Wed, Oct 14, 2009 at 8:38 AM, Aal-Rafizeldi <rafizeldi@...>
          wrote:
          >
          > >
          > >
          > > Thank you Sir, for remind me, i totally forgot about it
          > > this my query
          > > http://docs.google.com/View?id=df9tz3kc_49hk4vmgd6
          > > <http://docs.google.com/View?id=df9tz3kc_49hk4vmgd6>
          > >
          > > this is my query plan
          > > http://docs.google.com/View?id=df9tz3kc_47hkpcjwfr
          > >
          > > this is the related schema from my query
          > > http://docs.google.com/View?id=df9tz3kc_46g8pcsqdn
          > >
          > > any help/suggestion is really appreciated...
          > >
          > >
          > > --- In
          > SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogrou
          > ps.com>,
          > > "John Warner" <john@...> wrote:
          > > >
          > > > Without knowing the schema you ask a lot of us.
          > > >
          > > > John Warner
          > > >
          > > >
          > > > > -----Original Message-----
          > > > > From:
          > SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogrou
          > ps.com>
          > > > >
          > [mailto:SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yah
          > oogroups.com>]
          > > On Behalf Of Aal-Rafizeldi
          > > > > Sent: Tuesday, October 13, 2009 5:49 AM
          > > > > To:
          > SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogrou
          > ps.com>
          > > > > Subject: [SQLQueriesNoCode] Help-Optimize Query
          > > > >
          > > > > Dear all
          > > > > I am really new to complex sql query
          > > > > I need help to find another possibilities for this query
          > > > > there's so many subquery to skala_rental table
          > > > > Ho to decrease it? since skala_rental is the biggest table, more
          > > than 2
          > > > > million rows
          > > > > Is there any better query for this one?
          > > > >
          > > > > this is the query plan result queryplan.txt
          > > > > <http://f1.grp.yahoofs.com/v1/kEHUStgtfgm_jmD6DOY2DK-
          > > > > gZztvKuqq3gKSs7Gr5B\
          > > > >
          > yQJpJ2HI7ZZB8VGFSAqXKYl4WJ4qPecUVmYeA2uum9jN46km6RSlo/queryp
          > > > > lan-mri>
          > > > >
          > > > > select
          > > > > cf.cf_application_id as Id490_0_,
          > > > > '' as CfApplic2_490_0_,
          > > > > cf.cf_drawdown_number as Drawdown3_490_0_,
          > > > > '' as OldDrawd4_490_0_,
          > > > > p.full_name as Personal5_490_0_,
          > > > > c.name as Corporat6_490_0_,
          > > > > coalesce( (select name
          > > > > from lookup
          > > > > where lookup_id = pe.employment_status_id), 'NON GROUP'
          > > > > ) as Group490_0_,
          > > > >
          > > > > --principle +1 tahun, 2009/1/1
          > > > > coalesce( (select sum(principle)
          > > > > from skala_rental
          > > > > where cf_application_id = cf.cf_application_id
          > > > > and (payment_date is null or payment_date >
          > > '2008/1/1')
          > > > > and counter <> 0
          > > > > and due_date <= dateadd('year', 1, '2008/1/1')), 0
          > > > > ) as Principle8_490_0_,
          > > > > --interest +1 tahun, 2009/1/1
          > > > > coalesce( (select sum(interest)
          > > > > from skala_rental
          > > > > where cf_application_id = cf.cf_application_id
          > > > > and (payment_date is null or payment_date >
          > > '2008/1/1')
          > > > > and counter <> 0
          > > > > and due_date <= dateadd('year', 1, '2008/1/1')), 0
          > > > > ) as Interest12_490_0_,
          > > > >
          > > > > --principle (2009/1/1 + 1hari) - 2010/1/1
          > > > > coalesce( (select sum(principle)
          > > > > from skala_rental
          > > > > where cf_application_id = cf.cf_application_id
          > > > > and due_date between dateadd('day', 1, dateadd('year',
          > > 1,
          > > > > '2008/1/1'))
          > > > > and dateadd('year', 2, '2008/1/1')), 0
          > > > > ) as Principle9_490_0_,
          > > > > --interest (2009/1/1 + 1hari) - 2010/1/1
          > > > > coalesce( (select sum(interest)
          > > > > from skala_rental
          > > > > where cf_application_id = cf.cf_application_id
          > > > > and due_date between dateadd('day', 1, dateadd('year',
          > > 1,
          > > > > '2008/1/1'))
          > > > > and dateadd('year', 2, '2008/1/1')), 0
          > > > > ) as Interest13_490_0_,
          > > > >
          > > > > --principle (2010/1/1 + 1hari) - 2011/1/1
          > > > > coalesce( (select sum(principle)
          > > > > from skala_rental
          > > > > where cf_application_id = cf.cf_application_id
          > > > > and due_date between dateadd('day', 1, dateadd('year',
          > > 2,
          > > > > '2008/1/1'))
          > > > > and dateadd('year', 3, '2008/1/1')), 0
          > > > > ) as Principle10_490_0_,
          > > > > --interest (2010/1/1 + 1hari) - 2011/1/1
          > > > > coalesce( (select sum(interest)
          > > > > from skala_rental
          > > > > where cf_application_id = cf.cf_application_id
          > > > > and due_date between dateadd('day', 1, dateadd('year',
          > > 2,
          > > > > '2008/1/1'))
          > > > > and dateadd('year', 3, '2008/1/1')), 0
          > > > > ) as Interest14_490_0_,
          > > > >
          > > > > --principle > (2008 + 3)
          > > > > coalesce( (select sum(principle)
          > > > > from skala_rental
          > > > > where cf_application_id = cf.cf_application_id
          > > > > and due_date > dateadd('year', 3, '2008/1/1')), 0
          > > > > ) as Principle11_490_0_,
          > > > > --interest > (2008 + 3)
          > > > > coalesce( (select sum(interest)
          > > > > from skala_rental
          > > > > where cf_application_id = cf.cf_application_id
          > > > > and due_date > dateadd('year', 3, '2008/1/1')), 0
          > > > > ) as Interest15_490_0_,
          > > > >
          > > > > coalesce( (sr.unearned + sr.interest), 0) as Unearne16_490_0_,
          > > > > coalesce(sr.outstanding_balance, 0) as OsGross490_0_,
          > > > > (select number_of_digit
          > > > > from organization_setup
          > > > > where organization_id = cf.organization_id
          > > > > ) as NumberO18_490_0_
          > > > >
          > > > > from cf_application cf
          > > > > left join personal_main p on p.personal_id = cf.personal_id
          > > > > left join corporate_main c on c.corporate_id = cf.corporate_id
          > > > > left join personal_employment pe on pe.personal_id =
          > > p.personal_id
          > > > > join skala_rental sr on sr.cf_application_id =
          > > cf.cf_application_id
          > > > >
          > > > > where 1=1
          > > > > and cf.drawdown_date <= '2008/1/1'
          > > > > and (cf.terminate_date is null or cf.terminate_date > '2008/1/1')
          > > > > and sr.counter = (select min(counter)
          > > > > from skala_rental
          > > > > where cf_application_id = cf.cf_application_id
          > > > > and (payment_date is null or payment_date >
          > > > > '2008/1/1')
          > > > > and counter <> 0)
          > > > > and cf.branch_id = 2
          > > > > order by cf.cf_drawdown_number
          > > > >
          > > > > any idea?
          > > > > thank's very much
          > > > >
          > > > >
          > > > >
          > > > > [Non-text portions of this message have been removed]
          > > > >
          > > > >
          > > > >
          > > > > ------------------------------------
          > > > >
          > > > > Yahoo! Groups Links
          > > > >
          > > > >
          > > > >
          > > >
          > >
          > > [Non-text portions of this message have been removed]
          > >
          > >
          > >
          >
          > PR: wait... <javascript:{}> I: wait... <javascript:{}> L:
          > wait...<javascript:{}> LD:
          > wait... <javascript:{}> I: wait... <javascript:{}>wait...
          <javascript:{}> Rank:
          > wait... <javascript:{}> Traffic: wait... <javascript:{}> Price:
          > wait...<javascript:{}> C:
          > wait... <javascript:{}>
          >
          >
          > [Non-text portions of this message have been removed]
          >
          >
          >
          > ------------------------------------
          >
          > Yahoo! Groups Links
          >
          >
          >
        Your message has been successfully submitted and would be delivered to recipients shortly.