CODEWITHSUNDEEP
phpmysql
Vicky Gonsalves
Vicky Gonsalves

Reputation: 11717

How to copy rows from one table to another table with incremental custom field

I am trying to copy rows from one table to another table.
Tables: first is bollywood_videos_temp and other is bollywood_videos
Below is the Query to copy rows (Works Fine): 

   INSERT INTO `bollywood_videos`
    (`category`,
    `category_name`,
    `youtube_link`,
    `title`,
    `alias`,
    `short_description`,
    `long_description`,
    `youtube_video_id`,
    `original_poster`,
    `web_large_poster`,
    `web_small_poster`,
    `app_big_poster`,
    `app_small_poster`,
    `related_videos`,
    `sequence`,
    `status`,
    `published_date`,
    `created_by`,
    `date`)
    SELECT a.`category`,
    b.`type`,
    a.`youtube_link`,
    a.`title`,
    a.`alias`,
    a.`short_description`,
    a.`long_description`,
    a.`youtube_video_id`,
    a.`original_poster`,
    a.`web_large_poster`,
    a.`web_small_poster`,
    a.`app_big_poster`,
    a.`app_small_poster`,
    a.`related_videos`,
    ((SELECT MAX(sequence) AS sequence FROM bollywood_videos)) AS `sequence`,
    a.`status`,
    a.`published_date`,
    a.`created_by`,
    a.`date`
    FROM `bollywood_videos_temp` a
    JOIN bollywood_videos_categories b
    ON a.category=b.id
    WHERE a.`transaction_id`='2_1402492848'

Now My problem is, I m getting the same sequence integer as in figure attached below from the above subquery (SELECT MAX(sequence) AS sequence FROM bollywood_videos AS sequence) where I'm trying to fetch the max sequence from table bollywood_videos
Is there any such query which can give the MAX sequence from each and every row, I m struggling with this issue from last 5 hours.. Help would be appreciated! enter image description here

Upvotes: 0

Views: 77

Answers (1)

user20232359723568423357842364
user20232359723568423357842364

Reputation: 4634

Increment your sequence by adding 1

((SELECT MAX(sequence)+1 AS sequence FROM bollywood_videos)) AS `sequence`

.

INSERT INTO `bollywood_videos`
    (`category`,
    `category_name`,
    `youtube_link`,
    `title`,
    `alias`,
    `short_description`,
    `long_description`,
    `youtube_video_id`,
    `original_poster`,
    `web_large_poster`,
    `web_small_poster`,
    `app_big_poster`,
    `app_small_poster`,
    `related_videos`,
    `sequence`,
    `status`,
    `published_date`,
    `created_by`,
    `date`)
    SELECT a.`category`,
    b.`type`,
    a.`youtube_link`,
    a.`title`,
    a.`alias`,
    a.`short_description`,
    a.`long_description`,
    a.`youtube_video_id`,
    a.`original_poster`,
    a.`web_large_poster`,
    a.`web_small_poster`,
    a.`app_big_poster`,
    a.`app_small_poster`,
    a.`related_videos`,
    ((SELECT MAX(sequence)+1 AS sequence FROM bollywood_videos)) AS `sequence`,
    a.`status`,
    a.`published_date`,
    a.`created_by`,
    a.`date`
    FROM `bollywood_videos_temp` a
    JOIN bollywood_videos_categories b
    ON a.category=b.id
    WHERE a.`transaction_id`='2_1402492848'

Upvotes: 2

Related Questions