Updating a column based on other 2 column's values

Question

I have user_contents table. Here is the DDL

CREATE TABLE `user_contents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `content_type` int(11) NOT NULL,
  `order_id` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `user_contents_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
)

order_id is the newly added column. I need to update values of order_id based on the values of content_type and user_id. content_type can be 0 or 1.

Based on content_type and user_id i have to update order_id as shown in the above result. For same user_id and content_type order_id need to be incremented from 0.

Can some one help me with the update query

I am using mysql db of version 5.7.23-0ubuntu0.16.04.1

Edit : - - Now the requirement is slightly changed. Instead of data_type int for user_id, it is changed to varchar holding values like DAL001, HAL001 etc

Answer 1

Try the following query, to update order_id values. This employs User-defined session variables.

This query basically consists of two parts. First part determines order_id for every id, based on the defined logic.

Second part joins with the user_contents table using id and updates the order_id values.

UPDATE user_contents AS uc 
JOIN 
(
  SELECT 
    dt.id, 
    @oid := IF(@uid = dt.user_id AND 
               @ct = dt.content_type, 
               @oid + 1, 
               0) AS order_id, 
    @uid := dt.user_id, 
    @ct := dt.content_type 
  FROM 
  (
    SELECT 
      id, 
      user_id, 
      content_type
    FROM user_contents 
    ORDER BY user_id, content_type
  ) AS dt 
  CROSS JOIN (SELECT @oid := 0, 
                     @uid := 0, 
                     @ct  := 0) AS user_init_params 
) AS dt2 ON dt2.id = uc.id 
SET uc.order_id = dt2.order_id  

Answer 2

It would be better to use a view to achieve what you want. Here is one option which should work without window functions and without sessions variables:

CREATE VIEW user_contents_view AS (
    SELECT
        id,
        user_id,
        content_type,
        (SELECT COUNT(*) FROM user_contents uc2
         WHERE uc2.user_id = uc1.user_id AND
               uc2.content_type = uc1.content_type AND
               uc2.id < uc1.id) order_id
    FROM user_contents uc1
);

Demo

The main problem with suggesting to do an update here is that the order_id column apparently is derived data. This would mean that you might have to more updates again in the future. So, a view avoids this problem completely by just generating the output you want when you actually need it.