3
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
5
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
                 );
  • Than Q very much, This Information very Useful for me. – Pydi Raju Aug 22 '14 at 11:19
  • You're wellcome. In any stack exchange forum, if your question is answered, mark the reply as correct :) – JoseTeixeira Aug 22 '14 at 11:24
  • I tested this query , its working fine Than Q very much – Pydi Raju Aug 23 '14 at 4:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.