CODEWITHSUNDEEP
sqlsqlite
jobobo
jobobo

Reputation: 389

sqlite update (without join)

Pretty new to sqlite (and sql). Trying to modify one table using another.

create table Person(Key1 INTEGER, Key2 INTEGER, Name, IsDead, PRIMARY KEY (Key1,Key2));
create table Zombie(Key1 INTEGER, Key2 INTEGER, PRIMARY KEY (Key1,Key2));

I'd like to update IsDead column based on a list provided in Zombie. SQLite apparently can't use join with an update. Considering UPDATE or REPLACE statements. Thank you in advance.

As per @Tyler Ferraro, below is the solution.

UPDATE Person
SET IsDead = 1
WHERE EXISTS
(
SELECT *
FROM Zombie
WHERE Person.Key1 = Zombie.Key1 and Person.Key2 = Zombie.Key2
);

Below works for a single key, but I don't know how to handle composite keys.

UPDATE Person
SET IsDead = 1
WHERE Key1
IN
(
SELECT Key1
FROM Zombie
);

Upvotes: 0

Views: 1197

Answers (2)

Durai Amuthan.H
Durai Amuthan.H

Reputation: 32290

You could take advantage of INSERT OR REPLACE INTO statement.

INSERT OR REPLACE INTO Table1 
    (col1,col2,col3)
select Col1,Col2,Col3
from Table1 t1
inner join Table2 t2 on t1.Col1 = t2.Col1

if you face any duplicate records while using the statement create a unique index on one or more columns based on your logic then the duplication problem won't come up.

CREATE UNIQUE INDEX idxName ON Table1 (Col1,Col2)

Upvotes: 1

Tyler Ferraro
Tyler Ferraro

Reputation: 3772

I think you'll find the answer you're looking for here: Update table values from another table with the same user name

If you've got the proper Primary Keys setup, which you do, then this should be fairly straight forward.

Upvotes: 1

Related Questions