Professional Web Applications Themes

sql - statement for update - id - Coldfusion Database Access

Sorry, my knowledge in SQL is not much to cheer for. Hope anyone can help me with this statement: I have two column : employeeID and employeeName and DB is employee. In employeeName have 22 names. If i will add two new names and have two new ID, ie 23 and 24 but now I will take away id 23, this I want to see id move up to 23. How possible to write expression in sql? <cfquery name="utdata" datasource="palett"> select Max(employeeID) AS MaxID from employee </cfquery> <cfquery datasource="palett" name="ans"> SELECT foroefterNamn, employeeID FROM employee where employeeID = #utdata.MaxID# </cfquery> ...

  1. #1

    Default sql - statement for update - id

    Sorry, my knowledge in SQL is not much to cheer for.

    Hope anyone can help me with this statement:

    I have two column : employeeID and employeeName and DB is employee.

    In employeeName have 22 names.

    If i will add two new names and have two new ID, ie 23 and 24 but now I will
    take away id 23,

    this I want to see id move up to 23. How possible to write expression in sql?

    <cfquery name="utdata" datasource="palett">
    select Max(employeeID) AS MaxID
    from employee
    </cfquery>

    <cfquery datasource="palett" name="ans">
    SELECT foroefterNamn, employeeID
    FROM employee
    where employeeID = #utdata.MaxID#
    </cfquery>

    // Pilem



    pilem17 Guest

  2. #2

    Default Re: sql - statement for update - id

    What you want to do seems like a really bad idea, but it's your application.
    Assuming employeeid is not your primary key (in some databases, single field
    primary keys can't be updated), and it's numeric, this is your query

    update employee
    set employeeid = employeeid -1
    where employeeid >= 23

    Dan Bracuk Guest

  3. #3

    Default Re: sql - statement for update - id

    If your employeeID field is a primary key, and you EVER want to relate other
    entities to it with a foreign key, then what you want to do is a VERY VERY VERY
    bad idea. It sounds like you desperately need some lessons on data modeling,
    and how a relational database is supposed to work.

    [url]http://www.utexas.edu/its/windows/database/datamodeling/index.html[/url]

    Phil

    paross1 Guest

  4. #4

    Default Re: sql - statement for update - id

    I am only interested to see ordinary ID as MAX after employeeID 22 and if 23 is
    taken away, so it will be back in same id as 23
    when add new name.

    Because I have a locked list where 1-22 with name will not changed anything
    but if add new name so it doesn't matter when
    update or delete away this id 23 away from the datebase.

    That wat my question.



    pilem17 Guest

  5. #5

    Default Re: sql - statement for update - id

    Another alternative to update your Employee ID is as follows:

    First read all the records in the database:
    <cfquery name="ReadId" DataSource="DBSrc">
    SELECT * FROM Employee
    </cdquery>

    Suppose you have the delete query as:
    <cfquery name="deleteID" datasource="DBSrc">
    Delete FROM Employee
    Where employeeid=22
    </cfquery>

    Now you can have a loop that updates all the records from 23 onwards:

    <cfloop index="loop" from="23" to=#ReadID.RecordCount#>
    <cfquery name="UpdateID" DataSource="DBSrc">
    update employee
    set employeeid = employeeid -1
    where employeeid=#loop#
    </cfquery>
    </cfloop>

    This might not be the most efficient way, but its just a way to work around it.

    surenr Guest

  6. #6

    Default Re: sql - statement for update - id

    If I understand you question the way I would do it is.

    After you delete.....

    I did not test the syntax but it should give you an Idea.

    <cfquery name="EmpID" datasource="DBSrc">
    Select employeeid From Employee
    </cfquery>

    <cfset iID_Number = 1>
    <cfloop query="EmpID">
    <cfif employeeid GT iID_Number>
    <cfquery name="UpdateID" DataSource="DBSrc">
    update employee
    set employeeid = iID_Number
    where employeeid=#employeeid#
    </cfquery>
    </cfif>
    <cfset iID_Number = iID_Number+1>
    </cfloop>

    This should re-number your employee list.

    Rick



    "pilem17" <webforumsusermacromedia.com> wrote in message
    news:dhggva$p2h$1forums.macromedia.com...
    > Sorry, my knowledge in SQL is not much to cheer for.
    >
    > Hope anyone can help me with this statement:
    >
    > I have two column : employeeID and employeeName and DB is employee.
    >
    > In employeeName have 22 names.
    >
    > If i will add two new names and have two new ID, ie 23 and 24 but now I
    > will
    > take away id 23,
    >
    > this I want to see id move up to 23. How possible to write expression in
    > sql?
    >
    > <cfquery name="utdata" datasource="palett">
    > select Max(employeeID) AS MaxID
    > from employee
    > </cfquery>
    >
    > <cfquery datasource="palett" name="ans">
    > SELECT foroefterNamn, employeeID
    > FROM employee
    > where employeeID = #utdata.MaxID#
    > </cfquery>
    >
    > // Pilem
    >
    >
    >

    Rick Guest

Similar Threads

  1. Select-And-Update in one statement?
    By Ignoramus23298 in forum MySQL
    Replies: 1
    Last Post: May 19th, 07:02 PM
  2. Update Statement error
    By ldebono in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 22nd, 06:25 PM
  3. Generated Update Statement
    By Matt M. in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 30th, 04:41 PM
  4. update statement
    By John Bell in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: August 9th, 08:25 PM
  5. Update and Select Statement
    By hngo01 in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 4th, 01:32 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