How to update a specific field of a table without being related to another one by foreign?

Question

How to update a specific field of a table without being related to another one by foreign?

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have a little problem with this code and maybe someone can help me. It happens that I have three tables, loan, item, inventory. the loan and item tables are related, but the inventory is not, the fact is that when I make a loan, I have to decrease the amount in the felling element of the "element" that I am lending, well here it works to the perfection. the question is, how do I do it for at the same time I also decrease that amount in the inventory taking into account that inventory is NOT rationed with any table?

I have this method so far ...

public boolean AgregarPrestamo(int id_persona, int id_elemento, int id_curso, int cantidad_elem_pres, String fecha_hora_prestamo, String estado_inicial) {
    String consulta = "insert into prestamo values ("+null+","+id_persona+","+id_elemento+","+id_curso+","+null+","+cantidad_elem_pres+",'"+fecha_hora_prestamo+"',"+null+",'"+estado_inicial+"','"+null+"');";
    String consulta1 = "update inventario set ejemplares=ejemplares-"+cantidad_elem_pres+";";
    String consulta2 ="UPDATE elemento SET ejemplares=ejemplares-"+cantidad_elem_pres+" where id_elemento="+id_elemento;
    System.out.println(consulta);
    System.out.println(consulta1);
    System.out.println(consulta2);
    if (con.insertarDatos3(consulta,consulta1,consulta2)) {
        con.desconectar();
        return true;
    } else {
        con.desconectar();
        return false;
    }
}

The problem is that if I update the amount in the inventory table, but not the specific item, if not update the amount of all items in the inventory ...

I appreciate your help, greetings

mysql javascript mvc metodos

asked by Ferney Acevedo 20.05.2018 в 06:41

source

2 answers

Why does my onclick event mark me as undefined in my datatble? What is the best way to establish a value of a dropdown from the database [closed]

score 1 · Answer 1

Your structure could be as follows:

Inventario:                Elementos:          Prestamos

id                         id                  id

cantidad_disponible        id_inventario       id_elemento

estatus                    libro               fecha_prestamo

When you process a loan, the book you select from the table elements, you do the whole process of verification of existence, availability etc. If the book is available you will subtract 1 to quantity_available from the inventory table, that is done by obtaining id_inventario of the book you have chosen and do the UPDATE in the inventory table where the id is equal to the inventory_id you send.

I think that's what you need.

score 0 · Answer 2

It would be good to see what are the fields with which the tables count. In principle, what I see is that your update of the inventory table is general for all specimens, since it does not have a where :

String consulta1 = "update inventario set ejemplares=ejemplares-"+cantidad_elem_pres+";";

I should have it like this:

String consulta1 = "update inventario set ejemplares=ejemplares-"+cantidad_elem_pres+" where id_elemento=[id_elemento];";

I'm new to this but I think that's where the problem lies.