Update one table to another with Where?

Asked

Viewed 53 times

1

I have two tables one where the purchase order data and the second table where the items are.

Table A

id | ordem_compra | fornecedor | data

Table B

id | ordem_compra | desc | qtdade | valor | status | data

As the purchase order number is provided by the manufacturer of the item, it was all working until another industry ended up confirming us a purchase order with the same number from another factory which mixed the items when I query by the OC number.

I inserted another column in table B with the vendor code, my doubt now is as follows how to insert the vendor code of table A in table B.

I tried this way but unsuccessfully, what the right way?

UPDATE tbl_b
SET tbl_b.fornecedor = 
(
     SELECT tbl_a.id_fornecedor FROM tbl_a c
     WHERE tbl_a.ordem_compra = c.ordem_compra
);
mysql
  • 1

    But is that necessary? can put the supplier in the order and the items have a key with the order table, that if it is a numerical sequence there will be no problem, hence even the table B would not need the one of the order that is already in table A, avoids this information duplicity, that model is not standardised correctly

  • In fact, table B should have a foreign key for a unique value of table A, possibly tabela A.id. Keeping the purchase order value in both tables is a redundancy sign, which usually indicates that the table structure is not in the best way.

  • The way you did and, according to your statement, the ordem_purchase can have the same value for different suppliers, then you will also have to include supplier in table B. But since you use an artificial key (imagine id is an artificial key) then you can use such a field for the relationship but, eventually, you can generate some inconsistencies that you will have to prevent through code in your application (e.g. different id for the same pair ordem_purchase - supplier) which would not occur with the use of natural keys.

  • You are right I can put the id of table A in table B, but I didn’t understand how to do this query.

  • I edited the question by posting the way I tried to do Insert but without success, could guide me where I missed in the query?

  • Table B should have an id_tab_A field that would be the foreign key for the id of table A, and in this case it would not be necessary to order the field in table B. Or it should also have the vendor field, in case it uses natural keys. In both cases you need to modify the structure of your table B and reload the data.

Show 1 more comment

1 answer

4


From what I understand, you already have the data inserted in the two tables, but now you have inserted another column in table B and want to insert the suppliers in this new column. In this case, it would be right to do an update:

UPDATE tbl_b b, tbl_a a
   SET b.fornecedor = a.fornecedor
 WHERE b.ordem_compra = a.ordem_compra

Note that you should be careful when applying the update, since the two tables seem not to be normalized correctly!

  • In fact I was wrong and I was doing one INSERT where it was UPDATE. Thanks for the help, it worked perfectly, I’ve edited the question too.

Browser other questions tagged mysql

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