How to Reorder Rows in SQL Database

How to reorder rows using SQL (not swap)

DECLARE @id CHAR(1) = 'd';
DECLARE @newpos INT = 2;
DECLARE @oldpos INT;

BEGIN TRANSACTION
  SELECT @oldpos = Pos FROM Table WHERE id = @id;
  IF @newpos < @oldpos 
    UPDATE #Table SET Pos = Pos + 1 
     WHERE Pos >= @newpos AND Pos < @oldpos;
  ELSE
    UPDATE #Table SET Pos = Pos - 1 
     WHERE Pos >= @newpos AND Pos > @oldpos;

  UPDATE #Table SET Pos = @newpos
   WHERE Id = @id;
COMMIT TRANSACTION

Reorder rows in Table and then save the table in this order in mysql database

This is too long for a comment.

SQL tables represent unordered sets. There is no ordering. Period. If you run a SELECT query with no ORDER BY, then the results are in an indeterminate order. Even if they look like they are in some order (say insertion order), you cannot depend on that.

This is even true of a clustered index which stores the data on the data pages in a particular order. That ordering is not guaranteed.

What you can do is to store an additional column with the ordering you want and then use that in the ORDER BY:

alter table questions add column my_ordering int as
     (field(questionsId, 3, 2, 1));

Then you can easily use this for ordering:

select q.*
from questions q
order by my_ordering;

You can even use my_ordering in a column to speed up queries (on large data sets) that return results in that order.

Reorder rows in database

You need to change your foreign key on table database_name.oc_t_item_custom_attr_categories so that it updates along with column it references.

ALTER TABLE database_name.oc_t_item_custom_attr_categories DROP CONSTRAINT oc_t_item_custom_attr_categories_ibfk_1;
ALTER TABLE database_name.oc_t_item_custom_attr_categories
  ADD CONSTRAINT oc_t_item_custom_attr_categories_ibfk_1 FOREIGN KEY (fk_i_group_id)
      REFERENCES oc_t_item_custom_attr_groups (pk_i_id)
      ON UPDATE CASCADE;

Since MariaDB seem to not support ADDING foreign keys after table creation, this is how it should work for you, assuming description of tables is correct:

RENAME TABLE oc_t_item_custom_attr_categories TO oc_t_item_custom_attr_categories_2;

CREATE TABLE oc_t_item_custom_attr_categories (
  fk_i_group_id int(10) unsigned,
  fk_i_category_id int(10) unsigned,
  PRIMARY KEY(fk_i_group_id, fk_i_category_id),
  INDEX (fk_i_category_id),
  CONSTRAINT `oc_t_item_custom_attr_categories_ibfk_1` FOREIGN KEY (fk_i_group_id)
      REFERENCES oc_t_item_custom_attr_groups (pk_i_id)
      ON UPDATE CASCADE,
  CONSTRAINT `oc_t_item_custom_attr_categories_ibfk_2` FOREIGN KEY (fk_i_category_id)
      REFERENCES oc_t_category (pk_i_id)
) ENGINE = XtraDB; --change engine to what you are using

INSERT INTO oc_t_item_custom_attr_categories SELECT * FROM oc_t_item_custom_attr_categories_2;

How it works on example data in MySQL database: http://rextester.com/ZAKR50399

How to reorder records / rows in a MySQL table

In my view, you can't do it without having an extra column order

You will have to create a new column order for this purpose.

Then update the table as array is passed.

foreach($passed_array as $order => $val){
 $q = "Update table SET  `order` = $order WHERE ID = $val";
 // run query here
}

EDIT

And when displaying data at frontend, select query will be like

"SELECT * FROM table `Order` BY `order` ASC"

SQL: How to change a row order position

I'm not entirely clear how you intend for the reordering operation to work. Is this what you had in mind?

update T
set position =
    case    
        when newPosition > oldPosition then
            case when position = least(oldPosition, newPosition)
                then newPosition else position - 1 end
        else /* newPosition < oldPosition */
            case when position = greatest(oldPosition, newPosition)
                then newPosition else position + 1 end
    end    
where position between
           least(oldPosition, newPosition)
    and greatest(oldPosition, newPosition)
    and oldPosition <> newPosition

Reordering rows in sql database - idea

This is similar to a lexical order, which can also be done with varchar columns:


A
B
C
D
E
F

becomes


A
B
BM
C
D
F

becomes


B
BF
BM
C
D
F

I prefer the two step process, where you update every row in the table after the one you move to be one larger. Sql is efficient about this, where updating the rows following a change is not as bad as it seems. You preserve something that's more human readable, the storage size for your ordinal value scales in a linear rather with your data size, and you don't risk coming to a point where you don't have enough precision to put an item in between two values

SQL Rearrange row order

Just use CASE in the ORDER BY - it's much cleaner and easier to read.

...
ORDER BY CASE WHEN YourColumn = 4 then 0
              WHEN YourColumn = 2 then 1
              ELSE 2 END, yourcolumn

It's also a bad idea to change the order of the rows since there is actually no inherent order in SQL - it's all in the presentation layer, so use ORDER BY in your SELECTs to accomplish it.

Change order of rows

First, move down rows starting from 4:

UPDATE dbo.SecAccess SET SecID = SecID + 1 WHERE SecID > 3;

Then move the last row, now having the no. 9 to row 4:

UPDATE dbo.SecAccess SET SecID = 4 WHERE SecID = 9;

This assumes that SecIDis not an indentity column. If it is one then do

SET IDENTITY_INSERT dbo.SecAccess ON;
UPDATE dbo.SecAccess SET SecID = SecID + 1 WHERE SecID > 3;
UPDATE dbo.SecAccess SET SecID = 4 WHERE SecID = 9;
SET IDENTITY_INSERT dbo.SecAccess OFF;

See: SET IDENTITY_INSERT (Transact-SQL).

A solution giving you more flexibility is to introduce a new column solely used for sorting

ALTER TABLE dbo.SecAccess ADD SortOrder int;
UPDATE dbo.SecAccess SET SortOrder = CASE 
                                       WHEN SecID = 8 THEN 4
                                       WHEN SecID > 3 THEN SecID + 1
                                       ELSE SecID
                                     END;