Conditional SQL Update Statement?

Question

I have a table with hundreds of columns where some may be duplicated. What I want to do now is go back and add an index of sorts based on that column. For example:

OrderID
OOP-200221
POP-39808903
POP-39808903
POP-39808903
IIK-3993
IIK-3993
KOL-3939393930

I want to add an ID column that would show something like this:
1 - OOP-200221
1 - POP-39808903
2 - POP-39808903
3 - POP-39808903
1 - IIK-3993
2 - IIK-3993
1 - KOL-3939393930

(I used the dash to seperate the values, I just want the number in the new ID column and the OrderID to remain in the OrderID column.)

Is there a way to do this with a SQL statement?

Thanks in advance!

Answer 1

If you use phpMyAdmin, you can simply add a column in the front.

I will give you the SQL query however to add something to the beginning of the table.

Query:

ALTER TABLE `db_name` ADD `id` INT( 3 ) NOT NULL AUTO_INCREMENT FIRST



The FIRST at the end places that column to the beginning.

If you need further help, email me.

Good Luck!

Answer 2

Hello,



Please see the "source" link for the syntax highlighted version.



I assume your table has a unique index field, I will assume it's called `UniqueID`



First, create a new column for this:

ALTER TABLE `orders` ADD COLUMN `new_id`;





Now, here is the procedure:



DELIMITER //



CREATE PROCEDURE setIterate()

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE var_OrderID, var_OldOrderID CHAR(128);

DECLARE var_Iteration, var_UniqueID INT DEFAULT 1;

DECLARE cursah CURSOR FOR SELECT `OrderID`, `UniqueID` FROM `orders` ORDER BY `OrderID`, `UniqueID`;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;



OPEN cursah;



REPEAT

FETCH cursah INTO var_OrderID, var_UniqueID;

IF NOT done THEN



IF var_OrderID = var_OldOrderID THEN



SET var_Iteration = var_Iteration + 1;



ELSE



SET var_OldOrderID = var_OrderID;

SET var_Iteration = 1;



END IF;



UPDATE `orders` SET `new_id` = var_Iteration WHERE `UniqueID` = var_UniqueID;



END IF;

UNTIL done END REPEAT;



CLOSE cursah;

END//



CALL setIterate;





Hope this helps!

Answer 3

ASP.internet can purely run on a domicile windows internet server working IIS. own domicile page help many systems. ASP.internet is unquestionably greater powerful in my opinion however the limited platform help makes it much less many times occurring with programmers. There are some significant reward to ASP.internet nevertheless. in case you opt for to create the two a internet version and a computer version of your utility the only component you are able to recode is the types, the controls in those boards, and the code that drives those controls ought to be changed a splash. otherwise the code is very almost a hundred% transportable between laptop & internet server.

Answer 4

SELECT ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderID) ID, * FROM YourTable