Update a column with values from the same column SQL-CodePudding

I have a table trip. How can I update a trip_number if the trip_number is 0 and the trip_type is 'A' from a trip_type is 'B' and trip_number is not equal to 0 in the same table.

The table name trip.

Sample 1 : Sample 2 :

CodePudding user response：

You can use this query:

update test
set test.trip_number= (
    select
        test.trip_number
    from test
    where test.trip_type = 'B'
)
where test.trip_type = 'A'

Data before query:

And after the update 2 rows updated:

CodePudding user response：

Your update will look like the following:

update t
set trip_number= Coalesce((select Max(trip_number) from t where t.trip_type='B' and t.trip_number>0),0)
where t.trip_type='A' and t.trip_number=0