2 Replies - 628 Views - Last Post: 11 December 2010 - 11:21 AM

#1 3ck  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 09-June 09

Updating rows

Posted 07 December 2010 - 10:35 PM

Ok I need to iterate through the rows in my table, and increment them by one. Row 1 becomes 2, Row 2 becomes 3 etc. I then need to identify the last row and reasign it to the first. This code doesnt produce any SQL error but I am having difficulty isolating just the last row of the table and then reassigning them. Any ideas?

Thanks!


UPDATE tablename
	SET number = number+1
	ORDER BY number DESC;
	
UPDATE tablename
	SET number = 1
	WHERE number = (SELECT MAX(number))
	ORDER BY number DESC;



Is This A Good Question/Topic? 0
  • +

Replies To: Updating rows

#2 3ck  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 09-June 09

Re: Updating rows

Posted 07 December 2010 - 11:37 PM

Nevermind got it.
Was This Post Helpful? 0
  • +
  • -

#3 3ck  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 09-June 09

Re: Updating rows

Posted 11 December 2010 - 11:21 AM

View Post3ck, on 07 December 2010 - 09:35 PM, said:

Ok I need to iterate through the rows in my table, and increment them by one. Row 1 becomes 2, Row 2 becomes 3 etc. I then need to identify the last row and reasign it to the first. This code doesnt produce any SQL error but I am having difficulty isolating just the last row of the table and then reassigning them. Any ideas?

Thanks!


UPDATE tablename
	SET number = number+1
	ORDER BY number DESC;
	
UPDATE tablename
	SET number = 1
	WHERE number = (SELECT MAX(number))
	ORDER BY number DESC;






Keeping in mind with the helpful nature of this community, here is the way I found that works in completing this...
#Some of the show commands are for you to confirm the changes made as the script runs through


DROP TABLE IF EXISTS ranked_friends;
DROP TABLE IF EXISTS new_ranked_friends;
DROP TABLE IF EXISTS backup_ranked_friends;
DROP PROCEDURE IF EXISTS renumber;

CREATE TABLE ranked_friends (
rank_id INT AUTO_INCREMENT,
name VARCHAR(255),
address VARCHAR(255),
years_known YEAR(4),
PRIMARY KEY (rank_id)
);

#Sample Data to insert for QA check
INSERT INTO ranked_friends (rank_id, name, address, years_known) VALUES
(NULL,'JOHN DOE','12345 HILLY AVE SEATTLE WA 98125',2001),
(NULL,'JANE DOE','9999 WESTERN AVE SMALLBURG OH 43044',1999),
(NULL, 'HARRY POTTER','#12 GRIMMAULD PLACE LONDON',1997),
(NULL, 'SANTA CLAUS','1 NORTH POLE BLVD ARTIC CIRCLE',1980),
(NULL, 'SAM SMITH', '123 WINDING WAY SEATTLE WA 98125', 2005),
(NULL, 'HAN SOLO','10 HOLLYWOOD ST TATOOINE',1988),
(NULL, 'MASTER YODA','91 SWAMP ROCK CT, DAGOBAH',1920);
#END of Sample Data Insert for QA Check

#Prior to procedure
SELECT rank_id, name FROM ranked_friends
ORDER BY rank_id ASC;

#PROCEDURE BEGIN

DELIMITER //
CREATE PROCEDURE renumber()
BEGIN

#SELECT FUNCTION
SELECT name, address, years_known FROM ranked_friends 
ORDER BY years_known ASC;



#Not sure if you would want to mess around with the PK of a table, when a query can arrange
#the data however you would like it displayed.

#!!!!!RE-NUMBERING PROCESS BEGINS HERE!!!!!

#backup of original table before performing anything more dangerous than selects!

CREATE TABLE new_ranked_friends ( 
rank_id INT,
name VARCHAR(255),
address VARCHAR(255),
years_known YEAR(4)
)
SELECT * FROM ranked_friends;
#end of backup move

#shift rows
UPDATE new_ranked_friends
	SET rank_id = rank_id+1
	ORDER BY rank_id DESC;

#find last row	
UPDATE new_ranked_friends
	SET rank_id = 1
	WHERE rank_id = (SELECT MAX(rank_id))
	ORDER BY rank_id DESC
	LIMIT 1;

ALTER TABLE new_ranked_friends 
	ADD PRIMARY KEY (rank_id);
  
ALTER TABLE new_ranked_friends
	CHANGE COLUMN rank_id rank_id INT NOT NULL AUTO_INCREMENT;

#Swapping old ranked_friends to backup_copy and moving new table in.
RENAME TABLE ranked_friends TO backup_ranked_friends, new_ranked_friends TO ranked_friends;

#Line below is for QA so you can see the result for this test
SELECT rank_id, name FROM ranked_friends
ORDER BY rank_id ASC;

END//
DELIMITER ;
#END OF renumber() Procedure

CALL renumber();





I am open to any other possible ways as well.

Question: This may work on tables with a small number of rows, but what if you had a very large table? Would this still be the best way?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1