#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    8
    Rep Power
    0

    Deleting last record in a column


    Hi All

    Please see below table.

    Code:
    CREATE TABLE `commlogin` (   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   `profid` bigint(20) unsigned DEFAULT NULL,   `box` tinytext,   `batch` tinytext,   `userid` bigint(20) unsigned DEFAULT NULL,   `dt` datetime DEFAULT NULL,   `idx1t` tinytext,   `idx2t` tinytext,   `idx3t` tinytext,   `idx4t` tinytext,   `idx5t` tinytext,   `idx1dt` datetime DEFAULT NULL,   `idx2dt` datetime DEFAULT NULL,   `idx3dt` datetime DEFAULT NULL,   `idx4dt` datetime DEFAULT NULL,   `idx5dt` datetime DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `idxprofid` (`profid`),   KEY `idxbatch` (`batch`(255)),   KEY `idxuserid` (`userid`),   KEY `idxidx1t` (`idx1t`(255)),   KEY `idxidx2t` (`idx2t`(255)),   KEY `idxidx3t` (`idx3t`(255)),   KEY `idxidx4t` (`idx4t`(255)),   KEY `idxidx5t` (`idx5t`(255)),   KEY `idxidx1dt` (`idx1dt`),   KEY `idxidx2dt` (`idx2dt`),   KEY `idxidx3dt` (`idx3dt`),   KEY `idxidx4dt` (`idx4dt`),   KEY `idxidx5dt` (`idx5dt`),   KEY `idxbox` (`box`(255)) ) ENGINE=InnoDB AUTO_INCREMENT=8087131 DEFAULT CHARSET=latin1;

    In column batch the info stats 'BATCH0091S,BATCH0093S'.

    I need a script that will delete the 's' for me.

    Please help
    Thank you
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    do this with your application language (php or whatever)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    8
    Rep Power
    0
    Originally Posted by r937
    do this with your application language (php or whatever)
    Hi

    I don't have PHP, the Database administrator is on leave, was just hoping for an easy update statement if you know of one?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    Originally Posted by Blackghost
    was just hoping for an easy update statement if you know of one?
    if i knew of an easy update statement, would i have posted what i did?

    you can try this --
    Code:
    UPDATE commlogin
       SET batch = REPLACE(
                   REPLACE(batch,'BATCH0091S','BATCH0091')
                                ,'BATCH0093S','BATCH0093')
    you'll have to run something like that for all known values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo