Update order value in table - mysql select query has multiple joins

Question

I'm pretty good with mySQL but for some reason I'm struggling to get my head around this one.

I need to programmatically update the order values in the wp_esp_ticket table as the dates get out of order sometimes. This is the query I have to use to bring back all the dates that are related to each other and need ordering:

SELECT * FROM `wp_esp_ticket` et 
INNER JOIN wp_esp_datetime_ticket edtt on edtt.TKT_ID = et.TKT_ID
INNER JOIN wp_esp_datetime edt on edtt.DTT_ID = edt.DTT_ID
WHERE edt.EVT_ID = 1325

This brings back all the rows that need updating in the correct order, but the TKT_order column in the wp_esp_ticket table is then out of sequence. I need to give the top row a value of 1 in the column TKT_order and go up in increments of 1 for each row below.

I need to put together a tidy sql statement for this as it will need to be run along with another peice of code that adds new dates to the database.

Thanksss!!!

Update:

Thanks to the input and direction from Drew I got this to work:

SET @newnum = 0;

Update wp_esp_ticket tix

INNER JOIN (
SELECT TKT_ID FROM `wp_esp_ticket` et 
INNER JOIN wp_esp_datetime_ticket edtt on edtt.TKT_ID = et.TKT_ID
INNER JOIN wp_esp_datetime edt on edtt.DTT_ID = edt.DTT_ID
WHERE edt.EVT_ID = 1325
ORDER BY edt.DTT_EVT_start ASC
) b ON tix.TKT_ID = b.TKT_ID

SET tix.TKT_order = @newnum:=@newnum + 1

score 0 · Accepted Answer · edited May 23 '17 at 11:54

From the Manual near the top of the page.

For example, if the table contains 1 and 2 in the id column and 1 is updated to 2 before 2 is updated to 3, an error occurs. To avoid this problem, add an ORDER BY clause to cause the rows with larger id values to be updated before those with smaller values:

UPDATE t SET id = id + 1 ORDER BY id DESC;

Note that is a conceptual from the manual. Tweak accordingly.

Combine the update / order by with the concept of update with a join (How to do 3 table JOIN in UPDATE query?) and you are all set.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

In your case, I diverge, going with a rownum:

set @rownum:=0;

update `wp_esp_ticket` et 
INNER JOIN wp_esp_datetime_ticket edtt on edtt.TKT_ID = et.TKT_ID
INNER JOIN wp_esp_datetime edt on edtt.DTT_ID = edt.DTT_ID
set TKT_order =@rownum:=@rownum+1
WHERE edt.EVT_ID = 1325

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

see this rough example in action:

create table j
(   k varchar(20) not null,
    theOrder int not null
);

insert j(k,theOrder) values ('z',-1),('gj',-1),('w',-1),('h',-1),('uw',-1),('b',-1);

set @rownum:=0;
update j
set theOrder=@rownum:=@rownum+1
order by k;

+----+----------+
| k  | theOrder |
+----+----------+
| b  |        1 |
| gj |        2 |
| h  |        3 |
| uw |        4 |
| w  |        5 |
| z  |        6 |
+----+----------+

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Thanks Drew, any chance of a quick example of how I'd nest my select query in the update? — Zoe Epix, Aug 11 '15 at 10:53
Thanks Drew, did a little shuffling around and posted the solution above! Thank you!!! — Zoe Epix, Aug 11 '15 at 11:06

score 0 · Answer 2 · answered Aug 11 '15 at 10:43

0

Not sure if TKT_order is a existing column or a calculated column. If it's an existing column then what if you add a order by clause on TKT_order

SELECT * FROM `wp_esp_ticket` et 
INNER JOIN wp_esp_datetime_ticket edtt on edtt.TKT_ID = et.TKT_ID
INNER JOIN wp_esp_datetime edt on edtt.DTT_ID = edt.DTT_ID
WHERE edt.EVT_ID = 1325
ORDER BY et.TKT_order; <-- This one

answered Aug 11 '15 at 10:43

Rahul

76,197
13
71
125

TKT_order all ready exists but the numbers are wrong/out of sequence. I need to repopulate that column based on the date column asc... – Zoe Epix Aug 11 '15 at 10:53

Update order value in table - mysql select query has multiple joins

2 Answers2

Linked