Conditional SQL Update Statement?
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!
I’m trying to do this in SQL 2005 through a query. I guess I should have specified that.
I wasn’t able to get the query you supplied to work…
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!
November 25th, 2009 at 11:13 am
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!
References :
November 25th, 2009 at 11:23 am
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!
References :
http://pastebin.com/m3345b1e5
November 25th, 2009 at 11:29 am
SELECT ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderID) ID, * FROM YourTable
References :
http://www.sql-ex.ru/