CODEWITHSUNDEEP
mysqlsqlsqlite
SkySave
SkySave

Reputation: 11

Deleting rows in one table given values in another table -SQLite

I am working in a database containing ship information.

Table1 has frequently logged information on time, location and speed, including an ID for the vessels. Each vessel is logged several times in the database.

Table2 has fewer data points, and the information contains the ID and the ship type. It looks something like this:

Table1: 

Time -  Speed - Location - ID

10   -   10  -   X    -     111

12  -   10  -   X    -     112

30  -   11  -   X   -      111

42  -   10  -   X   -      113

51  -   12  -   X    -     114

59  -   12 -   X   -      112

67  -   14  -   X   -      114

81  -   13  -   X   -      111

90  -   10  -   X    -     113

96  -   13  -   X     -    114

...

Table2:

Time - ID -  Ship_type

15 -  111-  1

27  -  113-  12

40 -   112 - 4

73  -  111 - 1

80  -  114 -  18

87  -  112 -  4

97  -  113 -  12

What I want is to delete all the rows in Table1 where the Shiptype is above 10 for the corresponding ID in Table2. Say 1-10 are cargo ships and 10-20 are tankers, and I want all data on cargo ships from table one. Desired output:

Time -  Speed - Location - ID

10   -   10  -   X    -     111

12  -   10  -   X    -     112

30  -   11  -   X   -      111

59  -   12 -   X   -      112

81  -   13  -   X   -      111

Thanks

Upvotes: 0

Views: 1289

Answers (3)

Vladimir Ershov
Vladimir Ershov

Reputation: 45

select * from table1 where id in (select id from table2 where Shop_type > 9)

That will work for sure. But you also can try doing joins. It usually works faster then subquery.

Upvotes: 1

Binit Ghetiya
Binit Ghetiya

Reputation: 1979

As per your Question you need to link all tables using ID which is comman in all tables.

use can use left Join for that.

Select Query

SELECT * FROM Table1 as t1 
LEFT JOIN Table2 as t2 ON t1.ID = t2.ID 
LEFT JOIN Table3 as t3 ON t1.ID = t3.ID
WHERE 1 <= t2.Ship_type AND t2.Ship_type <=10;

Delete Query

DELECT FROM Table1 as t1 
LEFT JOIN Table2 as t2 ON t1.ID = t2.ID 
LEFT JOIN Table3 as t3 ON t1.ID = t3.ID
WHERE t2.Ship_type > 10;

Hope this works for you :)

Upvotes: 1

Jones Joseph
Jones Joseph

Reputation: 4938

Does that help

Select * from table1 where id in (Select id from table2 where shiptype between 10 and 20)

same goes as sub query for delete statement

Upvotes: 0

Related Questions