Jono
Jono

Reputation: 18118

SQLLITE move column and its data from TableA to TableB

i have two tables below

table_a with columns (id, table_bId, name, address, postcode, allowedPermisions)

table_b with columns (id, type, level, info)

I want to create a migration strategy as we now supposed to store the allowedPermissions data in table_b instead

Here is what i tried:

UPDATE table_a, table_b
SET table_a.allowedPermissions = table_b.allowedPermissions 
WHERE table_b.id=table_a.table_bId;

I get a syntax error after UPDATE table_a, (saw an example here saying you can update more than one table?)

I then tried another approach by creating the new column allowedPermissions using

ALTER TABLE table_b
ADD COLUMN allowedPermissions TEXT

But how do i copy contents of allowedPermissions in table_a to table_b in sqlite?

Upvotes: 1

Views: 52

Answers (1)

MikeT
MikeT

Reputation: 57043

Answer

I believe that the update would be :-

UPDATE table_b SET allowedPermissions = (SELECT allowedPermisions FROM table_a WHERE table_a.table_bId = table_b.id);

Additional

You could then perhaps use :-

CREATE TABLE IF NOT EXISTS table_a_copy  AS  SELECT id, table_bId, name, address, postcode FROM table_a;
ALTER TABLE table_a RENAME TO table_a_old;
ALTER TABLE table_a_copy RENAME TO table_a;
DROP TABLE IF EXISTS table_a_old;

To remove the redundant allowedPermisions column from table_a

Example

Consider the following demonstration :-

-- Create the original tables
DROP TABLE IF EXISTS table_a;
DROP TABLE IF EXISTS table_b;
CREATE TABLE IF NOT EXISTS table_a (id INTEGER PRIMARY KEY, table_bId INTEGER, name TEXT, address TEXT, postcode TEXT, allowedPermisions TEXT);
CREATE TABLE IF NOT EXISTS table_b (id INTEGER PRIMARY KEY, type TEXT, level INTEGER, info TEXT);

-- Populate the original tables
INSERT INTO table_b (type,level,info) VALUES
    ('TYEPA',1,'BLAH A'),('TYEPB',2,'BLAH B'),('TYEPC',1,'BLAH ')
;

INSERT INTO table_a (table_bId, name, address, postcode, allowedPermisions) VALUES
  (1,'Name A','ADDRESS A','OX120ND','READ'),
    (1,'Name M','ADDRESS M','SW050ND','READ'),
  (1,'Name X','ADDRESS X','FA120ND','READ'),
    (2,'Name Z','ADDRESS Z','OX120ND','WRITE')
;

-- Show what table_b looks like before the alter and update
SELECT * FROM table_b;

-- Alter an update table_b
ALTER TABLE table_b ADD COLUMN allowedPermissions;
UPDATE table_b SET allowedPermissions = (SELECT allowedPermisions FROM table_a WHERE table_a.table_bId = table_b.id);

-- Show what table_b looks like after the alter and update
SELECT * FROM table_b;

-- Clean up table_a (remove the allowdPermisions column)
DROP TABLE IF EXISTS table_a_copy;
CREATE TABLE IF NOT EXISTS table_a_copy  AS  SELECT id, table_bId, name, address, postcode FROM table_a;
ALTER TABLE table_a RENAME TO table_a_old;
ALTER TABLE table_a_copy RENAME TO table_a;
DROP TABLE IF EXISTS table_a_old;
SELECT * FROM table_a;

Example Result

The first result (table_b before the alter and update):-

enter image description here

The second result (table_b after the alter and update) :-

enter image description here

  • 3rd row is an orphan (by intention) hence the NULL

The third result (table_a without the allowedPermisions column) :-

enter image description here

Upvotes: 1

Related Questions