0

I have 3 tables: users, albums, photos. User can have albums, each album have photos. enter image description here

I want add at table [photos] new column userId with correct data. I want not only add empty userId column, but this column must be have value from table albums: userId.

How I can easy todo this?

Yura Shinkarev
  • 5,134
  • 7
  • 34
  • 57

1 Answers1

0

There is two tasks to do :

First, add the column to the table.

Once done, you will want to populate userId column. But, in SQLite, you cannot perform a JOIN in an UPDATE statement.

So you should cut it in four steps (I assume there is only 3 fields in your photos table, adapt it to your one).

First create a temporary table which will contain the final datas

CREATE TABLE photos_temp (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    albumId INTEGER NOT NULL,
    userId INTEGER NOT NULL
);

Then, populate the temporary table

INSERT INTO photos_temp(id, albumId, userId)
    SELECT photos.id as id,
           photos.albumId as albumId,
           albums.userId as userId
FROM       photos
INNER JOIN albums ON photos.albumId = albums.id;

Then, remove the original table

DROP TABLE photos;

Finally, rename the previous table

ALTER TABLE photos_temp RENAME TO photos;

Sorry, but no easier way to do this.