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

Question

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

Binit Ghetiya · Accepted Answer · 2016-11-18 10:21:34Z

1

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 :)

answered Nov 18, 2016 at 10:21

Binit Ghetiya

1,9792 gold badges22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SkySave Over a year ago

Thanks for the reply. The returned rows are a combination of Table1 and Table2, and I am getting multiple rows with equal Table1 columns. Say there is a record in Table1 at time 1, with ID 111. Then there exist three records on ID 111 in Table2, so the output is three columns at time 1 for ID 111. One for each of the Table2 records. The selection of ship types worked well though, and I want to go the left join-way for efficiency reasons, as I have a database of 160 GB

Binit Ghetiya Over a year ago

@SkySave If you want record in unique way then you can add GROUP BY t1.id at the end of the table.

Vladimir Ershov · Accepted Answer · 2016-11-18 11:10:36Z

1

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.

answered Nov 18, 2016 at 11:10

Vladimir Ershov

451 silver badge9 bronze badges

Comments

Jones Joseph · Accepted Answer · 2016-11-18 09:59:59Z

0

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

answered Nov 18, 2016 at 9:59

Jones Joseph

4,9583 gold badges27 silver badges42 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related