Reputation: 18118
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
Reputation: 57043
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);
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
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;
The first result (table_b before the alter and update):-
The second result (table_b after the alter and update) :-
The third result (table_a without the allowedPermisions column) :-
Upvotes: 1