CODEWITHSUNDEEP
phpmysqllaravel
Mark
Mark

Reputation: 59

How to Copy Column data from one table to another table in MySql?

I have two tables

  1. users
  2. activity

I have a column in the users table which is named as activity_date. Now, I want to add that column's data to my Activity table. For this, I introduce a new field in the Activity table named activity_date.

I am using this query to copy data from users table to activity table

DB::statement('UPDATE `activity` (`activity_date`) SELECT `activity_date` FROM `users` WHERE `users.id` = `activity.user_id`');

When I run the migration, It returns an error

[PDOException]                                                                      
SQLSTATE[233652]: Column not found: 1054 Unknown column 'users.id' in 'where clause'

I never tried this before. May someone please guide me on how to do this? Where I am doing wrong?

Upvotes: 0

Views: 57

Answers (2)

Eryngium
Eryngium

Reputation: 3

update users set activity_date=(SELECT activity_date from activity where id='1')where id='1'

This will take acitivity_date with ID=1 from table activity, and update acitivity_date in users table where user id=1.

tables: (users): id, name, activity_date (activity) id, activity_date

Upvotes: 0

OMR
OMR

Reputation: 12188

you should join your tables then use DB::raw

DB::table('activity')->join('users','activity.user_id','=','users.id')
        ->update(['activity.activity_date'=>DB::raw('users.activity_date')]);

Upvotes: 1

Related Questions