How to insert multiple Rows, where some Rows will have fewer values than columns

Asked

Viewed 36 times

-1

I’ll try to give a simple example of my problem.

I have the following columns in a table called "Test":

Name | Age | Gender | Nota1 | Nota2 | Nota3

I want to insert multiple lines in the table, but not all students will have all grades. Example:

INSERT INTO "Teste" ("Nome", "Idade", "Nota1", "Nota2", "Nota3") VALUES("Caio","19", 10, 9, 8), ("João","17", 10, 9);

I wonder if you have any way of making that insertion.

NOTE: I could put null or an empty string(" "), but I can’t.

From now on, thank you!

sql db2
  • 1

    Someone could, for example, have grades 2 and 3 but not have the 1?

  • Yes. Students can have 1 of the 3, none and so on.

  • If the missing note can be any of the three I do not see how you inform such a situation other than by not informing the (s) field(s) corresponding to(s) the missing note(s) in the field list or by informing NULL in the value list (s) corresponding to(s) to(s) note(s) absentee(s). There’s no way the BDS knows without you explicitly informing.

  • So there’s no way to do what I want?

1 answer

1

There is no syntax for inserting multiple lines, where one line affects certain fields, and another line affects other fields.

Each INSERT must specify the columns to be inserted, and all values will refer to those columns.

In the background, for each line to refer to different fields, we would have to explain to each line what should be done, so in the background this is reduced to the problem of making an INSERT for each situation. Since in this case, I agree that there is a simplification, because the columns not to be inserted would always be the last ones, so it would be like sending a shorter list of values. Anyway, it’s not possible.

However, the restriction that you cannot send NULL or empty string does not make much sense. For, if the database agreed to provide fewer columns in the INSERT, it would mean that the other columns would receive a default value when entering the record in the database. Normally this value would be NULL even, so why not put it in INSERT itself?

Perhaps another solution would be to renormalize the database to have, on the one hand, the attributes of the person in a table, containing name and age. And, in another table, the description of the grade ('Note 1', 'Note 2'), etc and its value, in addition to the student’s identification. So notes could be inserted only when available.

  • Thanks for the answer. In the end I had to restructure the database with some different rules. But so far I do not understand what I did wrong haha, but we will learn over time... Thank you!

Browser other questions tagged sql db2

You are not signed in. Login or sign up in order to post.