Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Location
    United Kingdom, Liverpool
    Posts
    115

    MSSQL Expert needed for a little help

    Well, i've come accross some issues with my database, i need a fix. One of the programms we use writed to the database. There's been quite a few errors (it created double IID's (Stands for item id). I need a query to search for double Item ID's and replace them with non existing IID.

    "Select IID and find double rows. If found then give them different values which are not already exisiting there."

    That might not make sense to some people, i can't really explain myself.. but any help would be appreciated.
    Linux/Windows Technician
    JCulpin [at] Gmail.com

  2. #2
    Join Date
    Mar 2004
    Location
    Singapore
    Posts
    6,984
    This is best in the Programming Forum. Moved.

  3. #3
    Join Date
    Sep 2004
    Posts
    105
    To find the double rows I'd use
    Code:
    select iid, count(iid) from table group by iid having count(iid)>1
    To change them, maybe something like this (though if two rows have IID of 14, this query will change both of them):
    Code:
    update table set iid=(select max(iid)+1 from table) where iid in (select iid from table group by iid having count(iid)>1) x
    I'm not 100% sure that second query will do what you want it to but it's somewhere to start. Actually, now that I think of it, it probably won't. You'll probably need to make a stored procedure or a script (ASP, PHP, etc) to accomplish this. Of course, if IID is a foreign key in other tables, they'll have to be updated as well, and probably manually to make sure you're updating the correct records.

  4. #4
    Join Date
    Dec 2005
    Location
    United Kingdom, Liverpool
    Posts
    115
    Thanks alot, i haven't tried it yet. Can you that into a php script? i'd be willing to pay you. I did try that query, nothing came up though. i used.

    SELECT IID, COUNT(IID) AS EXPR1
    FROM Item
    GROUP BY IID
    HAVING (COUNT(IID) > 1).

    I thought i might add. Item IDs have negative and positive numbers.
    Last edited by Jake; 12-26-2006 at 02:07 PM.
    Linux/Windows Technician
    JCulpin [at] Gmail.com

Posting Permissions

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