Using Delete, Update, Insert in MySQL

Question

Good day!

First of all, I am new at MySQL, and I've got some problems with altering tables with query's.

Secondly, sorry for bad English, unfortunately, it's not my native language.

So yeah, I have some questions, and I will be very thankful if you will be able to help me.

I have 2 tables: Screenshot

Foreign key is 'manufact' from table Registred

So, I have few Query tasks I wanted to do, but can't figure out how to do them.

Here goes first task I did, I just want you guys to check, If I did it right.

1. Calculate fields from January and February where Car Manufacturer is VW.

   SET Total = January + February WHERE Manufact = 3;

And here comes Query's I can't do:

1. Delete all information about car manufacturer, that had smallest amount of cars registred in January.

Here is what I came up with.

DELETE FROM Registered Order by January Limit 1;

But it didn't delete information from table 'manufacturer'.

What can I do there?

2. Here is hardest one so far: Calculate all cars registered in January and February and save it in additional field. (Should be displayed as Int, without floating point.)

Code:

ALTER TABLE Registered Add Column alltotal Int;
UPDATE Registered
Set alltotal = Select SUM(February + January) From Registered;

What I wanted to do is, Create only 1 field, where Sum of all February and January fields will be calculated.

Any suggestions so far?

P.S If I will be able to do these, I will be able to complete other tasks myself :)

P.S.2 I am new here, so please sorry for bad Question editing. I am doing as in tutorial I found but it's hellish for me. I tried my best :)

Answer 1

Let's break this down problem by problem. The first one is:

Delete all information about car manufacturer, that had smallest amount of cars registered in January.

The first thing we need to do is write a subquery that determines which manufacturer that is. We can do so by selecting the id of the row in registracija with the minimum value for January. We can do that using ORDER BY, which you caught on to:

SELECT vieglas
FROM registracija
ORDER BY january
LIMIT 1;

Now that we have that id, we can delete from that table using the WHERE clause:

DELETE FROM registracija
WHERE vieglas = (SELECT vieglas FROM (SELECT * FROM registracija) t ORDER BY january LIMIT 1);

For information on why I included the SELECT * FROM registracija, see this answer.

To see an SQL Fiddle of that in action, check this: http://sqlfiddle.com/#!9/c81d5/1

For the second part:

Calculate all cars registred in January and February and save it in additional field. (Should be displayed as Int, without floating point.)

We can use the addition operator along with an update command to put this total inside the Total column like this:

UPDATE registracija
SET total = (january + february);

For the SQL Fiddle of the update, see this: http://sqlfiddle.com/#!9/f5b28/1

Answer 2

for deleting all record which has had smallest amount of cars registred in January delete from 'Registracija' where 'January' =select min('January') from Registracija

Answer 3

Try following:

1) Total Count For January & February

SELECT (January+February) AS TOTAL from Registrant where Vieglas = 3

2) Delete Query

DELETE FROM Registrant INNER JOIN Vieglas Order By Registrant.January Limit 1