Determine the updated row sequence following a DELETE operation

I am currently developing an iOS and Android app using PhoneGap. In my project, I am utilizing WebSql and encountering a certain issue: How can I rearrange the values in a column named 'position' when a row is deleted? For example, let's say we have the following base table:

╔═════╦══════════╗
║ ID  ║ POSITION ║
╠═════╬══════════╣
║  23 ║        0 ║
║  32 ║        1 ║
║ 143 ║        2 ║
║  13 ║        3 ║
╚═════╩══════════╝

If I were to delete the row with the position value of 1, the positions of the remaining rows should automatically decrement like so:

╔═════╦══════════╗
║ ID  ║ POSITION ║
╠═════╬══════════╣
║  23 ║        0 ║
║ 143 ║        1 ║
║  13 ║        2 ║
╚═════╩══════════╝

My question is: Is there a way to achieve this directly in MySql after deleting a row, or do I need to handle this logic in JavaScript by deleting the row, querying all the rows, looping through them to adjust their positions, and then updating the rows accordingly?

Any assistance on this matter would be greatly appreciated!

javascriptmysqlsqlsql-updatesql-delete

Answer №1

Once the delete operation has been completed, proceed to execute this update query in order to adjust the values of the position field.

UPDATE  tableName a
        INNER JOIN
        (
            SELECT  a.id, @rn := @rn + 1 New_position
            FROM    tableName a, (SELECT @rn := 0) b
            ORDER   BY a.Position
        ) b ON  a.ID = b.ID
SET     a.Position = b.New_Position
  • Check out the SQLFiddle Demo here

The outcome following the completion of the above task is as follows:

╔═════╦══════════╗
║ ID  ║ POSITION ║
╠═════╬══════════╣
║  23 ║        1 ║
║ 143 ║        2 ║
║  13 ║        3 ║
╚═════╩══════════╝

Answer №2

In my situation, I found that utilizing localStorage proved to be a more convenient and efficient option compared to WebSql.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Can someone please tell me the best way to retrieve unique rows based on a specific column

I need to retrieve the last 5 records inserted in a database from 10 different users. If the same user inserted the last 3 rows, I only want one row per user until I reach a count of 5. Consider a database structured like this: user_id | news_id | title ...

phpmysqlsqldistinct

Combining rows using a specific column in Javascript

Could this particular question already be in existence, but just hasn't been found yet? Is there a method for consolidating multiple values into one row of a 2D array with x rows and 2 columns based on them sharing the same element in the first column ...

javascriptarrays

Submitting a form using ajax to send form data to php without reloading the page

Could anyone please help me figure out why my code is not functioning properly? I am trying to send form data to PHP using the post method, but my PHP script is not receiving any requests. Below is the code snippet for my signup form located in signupmodal ...

javascriptphphtmljquerybootstrap-4

Best practices for establishing a conditional statement with JQuery's inArray function

I am working on a code snippet to generate a list of unique random numbers. Each generated number is supposed to be added to an array after checking that it doesn't already exist in the array. However, I seem to be facing some challenges with getting ...

javascriptjqueryarrays

Automatically submitting a form in React.js based on certain conditions being met

Does anyone have experience with React login and register form buttons that trigger Redux actions? I'm facing an issue where both actions are being dispatched at the same time when certain conditions are met. Here is my code snippet: const LoginPage ...

javascriptreactjsredux

Querying the same table to find related records

Here are some sample records: Custid pref_location C1 PUNE C1 MUMBAI c1 DELHI C2 PUNE C2 MUMBAI C3 MUMBAI c4 PUNE C5 DELHI I am trying to retrieve records where the preferred location is only PUNE or MUMBAI. Currentl ...

sqloracleperformance

Highcharts introduces shared tooltips for specific data series

I am seeking to implement specific behavior in highcharts regarding tooltips. The desired setup includes having two types of tooltips: the default shared tooltip a custom tooltip For the custom tooltip, a simple formatter can be utilized. However, the c ...

javascripthighcharts

unable to adjust the maximum height limit

I've been struggling to set a maximum height on the slider I'm currently using. No matter what height value I input, it doesn't seem to take effect. Additionally, I attempted setting the width for the echo img row in the PHP section but enco ...

javascripthtmlcss

Tips on sending a list via @Input using Angular5

I was seeking guidance on how to pass a list through an input. In my parent component, I have the following: list: Hero[] = [{Id: 2, Name: "Sll"}, {Id: 3, Name: "Sldsadasd"}] and in the HTML: <app-add-hero list="{{list}}" hero={{hero}}></app-ad ...

javascriptangular

Line numbers in Vue Codemirror

Currently, I'm working on integrating codemirror into my Vue.js application using a library found at https://github.com/surmon-china/vue-codemirror. The issue I'm facing is that even after importing and utilizing the codemirro component, everythi ...

javascriptvue.jscodemirror

When the react autocomplete dropdown appears, it pushes other input elements downward, affecting their

Having implemented an autocomplete dropdown feature by following this link, I am encountering an issue with displaying the dropdown items. As shown in the reference link, the dropdown items are rendered within a div tag with the position set to relative. ...

javascripthtmlcssreactjsautocomplete

Perform Action Only When Clicking "X" Button on JQuery Dialog

I have a dialog box with two buttons, "Yes" and "No", which trigger different functions when clicked. $('#divDialog').dialog({ modal:true, width:450, resizable: false, buttons: [{ text: 'Yes', ...

javascriptjqueryjquery-ui

Issues with IE 11: SVG Map Not Triggering Mouseenter or Mouseleave Events

I've been grappling with this issue for the past couple of days and despite trying numerous solutions, nothing seems to be working. My SVG map of the US has jQuery mouseenter and mouseleave events that function properly in browsers other than IE 11 ( ...

javascriptjquerycssinternet-explorersvg

Using Django template variables within JavaScript linked to the template

How can I access Django template variables like {{ STATIC_URL }} in the referenced file.js that is included using <script src="/static/file.js" /> in my template? Looking for the best approach to provide access to template variables in file.js. Any ...

javascriptdjangodjango-templates

Using axiosjs to send FormData from a Node.js environment

I am facing an issue with making the post request correctly using Flightaware's API, which requires form data. Since Node does not support form data, I decided to import form-data from this link. Here is how my code looks like with axios. import { Fl ...

javascriptnode.jstypescriptaxios

What is the best way to pass the "$user" variable in a loadable file using ajax?

Is there a way to send the variable user to the mLoad.php file before executing this script? Any suggestions would be greatly appreciated. Thank you! $(document).ready(function(){ $("#message_area").load('mLoad.php'); $("#userArea").submit(func ...

javascriptphpjqueryajax

When attempting to log an AJAX/JSON request, the console.log statement is displaying 'undefined'

Being new to AJAX, I have encountered an issue that I believe others may have faced as well. Despite numerous attempts and extensive research, I am unable to find a solution to this error. I am confident that it is something simple. Any help would be gre ...

javascriptjsonajax

Bug in canvas rendering for Chrome versions 94 and 95

The Canvas drawing functionality in the Chrome browser seems to have some visible bugs. Here's an example of code that demonstrates this issue: const canvas = document.getElementById('canvas'); const ctx = canvas.getContext('2d&apo ...

javascripthtmlgoogle-chromecanvas

What purpose do controller.$viewValue and controller.$modelValue serve in the Angular framework?

I'm confused about the connection between scope.ngModel and controller.$viewValue/controller.$modelValue/controller.$setViewValue(). I'm specifically unsure of the purpose of the latter three. Take a look at this jsfiddle: <input type="text" ...

javascriptangularjsangularjs-directiveangular-ngmodel

How to include a comment on a table column in Sequelize

I'm curious, is it feasible to include column comments while creating a table using sequelize? After reading the information here, I found out that you can only add a comment to a table. But how would one go about adding a comment to individual colum ...

mysqlnode.jssequelize.js