CODEWITHSUNDEEP
mysqlsqlsql-update
Seena V P
Seena V P

Reputation: 994

Updating a column based on other 2 column's values

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.

Expected Result

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

Upvotes: 4

Views: 82

Answers (3)

Reshma
Reshma

Reputation: 21

string SQL = "SELECT MAX(order_id) FROM user_contents 
WHERE user_id = 'label1' AND content_type ='label2'";

string sql = "UPDATE user_contents SET order_id='" +bb+ "' WHERE sl='1'";

After getting maximum order id increment the orderid and pass to some variable and update using update query.

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28874

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

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

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
);

enter image description here

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.

Upvotes: 3

Related Questions