SQLite – Using Joins in Update Statements

join;sqliteupdate

CREATE TABLE Table_1 (
    Id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
    EmailId VARCHAR
)

INSERT INTO Table_1 VALUES (1,'raj@gmail.com');
INSERT INTO Table_1 VALUES (3,'test@gmail.com');
INSERT INTO Table_1 VALUES (4,'false5@gmail.com');
INSERT INTO Table_1 VALUES (7,'false55@gmail.com');
INSERT INTO Table_1 VALUES (8,'falsefalse@gmail.com');
INSERT INTO Table_1 VALUES (9,'true1@gmail.com');
INSERT INTO Table_1 VALUES (11,'true2@gmail.com');
INSERT INTO Table_1 VALUES (12,'falsefalsefalse@gmail.com');
INSERT INTO Table_1 VALUES (13,'ravi@gmail.com');
INSERT INTO Table_1 VALUES (14,'rak@gmail.com');
INSERT INTO Table_1 VALUES (15,'false@gmail.com');
INSERT INTO Table_1 VALUES (16,'false66@gmail.com');


CREATE TABLE Table_2 (
    Id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
    EmailId VARCHAR
)

INSERT INTO Table_2 VALUES (1,'raj@gmail.com');
INSERT INTO Table_2 VALUES (3,'test@gmail.com');
INSERT INTO Table_2 VALUES (4,'false98@gmail.com');
INSERT INTO Table_2 VALUES (7,'abc@gmail.com');
INSERT INTO Table_2 VALUES (8,'ta@gmail.com');
INSERT INTO Table_2 VALUES (9,'false1@gmail.com');
INSERT INTO Table_2 VALUES (11,'false2@gmail.com');
INSERT INTO Table_2 VALUES (12,'false@gmail.com');
INSERT INTO Table_2 VALUES (13,NULL);
INSERT INTO Table_2 VALUES (14,NULL);
INSERT INTO Table_2 VALUES (15,'false@gmail.com');
INSERT INTO Table_2 VALUES (16,'false66@gmail.com');

I have two tables in sqlite like Table_1 and Table_2

I need to update Entire Table_1 EmailIds Using table_2 EmailIds based on below conditions

condition-1)

  • need to udpate when Table_1 EmailId<>table_2 EmailId (to all rows)

condition-2)

  • If Table_2 EmailId is null don't need to update that null value in
    Table_1 partucular EmailId

as per the Above Tables with my requirement I want below output:–

1) I want to update 4,7,8,9,11,12 Id's

2) I don't want update 1,3,15,16 Id's(because same emailids in both tables)

3) I don't want update 13,14 Id's (because 13, 14 emailid are null values in Table_2)

For the above problem I wrote below query in MYSQL and SQL Server it's working fine, but in sqlite it's not working.

I found Joins will not work in Update statement in sqlite but I want an alternate solution. Can anybody help me please?

UPDATE Newcontacts n
  JOIN DummyContacts d  ON (n.Id=d.Id)
SET n.EmailId=d.EmailId
WHERE n.EmailId<>d.EmailId
  OR d.EmailId IS NOT NULL

Best Answer

UPDATE Table_1
SET EmailId = (SELECT d.EmailId
                 FROM Table_2 d
                 WHERE Table_1.Id = d.Id
                 )
WHERE EXISTS (SELECT d.EmailId
                 FROM Table_2 d
                 WHERE Table_1.Id = d.Id
                   AND Table_1.EmailId <> d.EmailId
                   AND d.EmailId IS NOT NULL
                 );
Related Question