sql - statement for update - id

[pilem17]

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

[Dan Bracuk]

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

[paross1]

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

[pilem17]

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.

[surenr]

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.

[Rick]

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" <webforumsuser@macromedia.com> wrote in message
news:dhggva$p2h$1@forums.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
>
>
>