Professional Web Applications Themes

Help with a query Urgent - Microsoft SQL / MS SQL Server

Removed by Administrator...

  1. Moderated Post

    Default Help with a query Urgent

    Removed by Administrator
    Trevor Guest
    Moderated Post

  2. #2

    Default Re: Help with a query Urgent

    To do this you need to know the primary key of the table. Assuming you have
    a single column PK called Keycol:

    UPDATE bac
    SET bac.numero =
    (SELECT COUNT(*)
    FROM bac AS B
    WHERE keycol<=bac.keycol
    AND dispo='y')
    WHERE dispo = 'y'

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: Help with a query Urgent

    Well if its just a stand alone table as such with no indexes ,etc.. heres
    one quick way of doing it

    --Creates another table BACtemp with all the requested data you need

    Select dispo,identity(int,1,1) as numero
    into BACtemp from BAC
    -- Drops the originaltable 'BAC'
    Drop object BAC
    --Renames the new table to the old name
    exec sp_rename 'BACtemp', 'BAC'

    But test before running in production


    "Trevor BROOKES" <fr> wrote in message
    news:phx.gbl... 
    > have 
    >
    >[/ref]


    FR Guest

  4. #4

    Default Re: Help with a query Urgent

    Actually, here is an easier way:

    DECLARE cnt INTEGER
    SET cnt = 0

    UPDATE bac
    SET cnt = numero = cnt + 1
    WHERE dispo = 'y'

    When your programmer gets back tell him to fix the database. Every table
    should have a primary key without fail.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  5. #5

    Default Re: Help with a query Urgent

    Here's an possible answer using that Bad word CURSOR

    Tim S

    DECLARE cnt int

    DECLARE test_cursor CURSOR
    FOR SELECT numero FROM bac WHERE DISPO = 'Y'
    FOR UPDATE OF numero

    OPEN test_cursor

    SELECT cnt = 1

    FETCH NEXT FROM test_cursor

    WHILE FETCH_STATUS = 0
    BEGIN
    UPDATE bac SET numero = cnt
    WHERE CURRENT OF test_cursor

    SELECT cnt = cnt + 1
    FETCH NEXT FROM test_cursor
    END

    CLOSE test_cursor

    DEALLOCATE test_cursor




    "FR" <com> wrote in message
    news:phx.gbl... [/ref]
    meant 
    > > have 
    > >
    > >[/ref]
    >
    >[/ref]



    Tim Guest

  6. #6

    Default Re: Help with a query Urgent

    Everything is now working fine

    Thanks for the help in this matter and I will get my programmer to add the
    primary key as soon as he gets back

    Again thanks to all for the help

    Trevor


    "Tim S" <com> a écrit dans le message de
    news:PjLRa.6062$atl2.webusenet.com... [/ref]
    heres [/ref]
    > meant [/ref][/ref]
    le [/ref][/ref]
    you 
    > >
    > >[/ref]
    >
    >
    >[/ref]


    Trevor Guest

  7. #7

    Default Re: Help with a query Urgent

    >> The table BAC has only two columns DISPO and NUMERO <<

    Without a key, this is not a table be defintion. This should not have
    ever been possible. You might want to keep the programmer on holiday
    permanently; he does not know what he is doing.

    Write a list of INSERT INTO statements with a text editor and put values
    1-1404 back into the table. Flush out the junk. Then add a PRIMARY KEY
    constraint to "numero" and later get a competent progbram to add
    REFERENCES clauses. Better yet, get this whole datsabase redesigned
    correctly.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

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

Similar Threads

  1. Creating data query from mySQL stored query!?
    By johnegbert in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 5th, 09:28 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Need Urgent assistance with a SQL Query
    By Sandeep Commar in forum Microsoft SQL / MS SQL Server
    Replies: 10
    Last Post: July 23rd, 07:01 AM
  4. Replies: 1
    Last Post: July 2nd, 09:09 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