我需要帮助,我在mysql中有一个表,变量Flight_id看起来像:

flight_id
1R10005
1M10000000000
1M10000000001
1M10000000002
1M10000000003
1M10000000004
1M10000000005
1M10000000006
1M10000000007
1R10006
1M10000000008
1M10000000009
1M10000000010
1M10000000011
1R10007
1M10000000012
1M10000000013
1M10000000014
etc....


我想将所有以'1M100'开头的flight_id替换为flight_id'1R1'
我想获得这样的表:

1R10005
1R10005
1R10005
1R10005
1R10005
1R10005
1R10005
1R10005
1R10005
1R10006
1R10006
1R10006
1R10006
1R10006
1R10007
1R10007
1R10007
1R10007


有人可以帮我mysql查询吗:)

最佳答案

您需要一个ID或其他一些列来订购表。如果您有ID,则可以使用以下方式:

UPDATE
  flights inner join (
    select f1.id, max(f2.id) as new_id
    from flights f1 left join flights f2
         on f1.flight_id like '1M100%'
            and f2.flight_id like '1R1%'
            and f1.id>f2.id
    group by f1.id) fmax
  on flights.id=fmax.id
  inner join flights fdesc
  on fmax.new_id=fdesc.id
SET
  flights.flight_id = fdesc.flight_id


内部子查询针对每个航班ID返回new_id,这是从中获取说明的idnew_id是当前行的最大id
09-25 18:17