Update tables with the same data in the row

Asked

Viewed 147 times

1

When I add a category A, and add a product X with the category A (table categories), then edit the category name A for category B, the category of product X (products table) remains with the category A.

My update query is

UPDATE Categorias set Categoria = @Categoria WHERE IDCatg = @IDCatg

Basically wanted that when the category A was edited (table categories), check if there is any product with the category A (products table), and in addition to update to the category A to the B, edit the product category X for B. If the category is not in any product, edit normally, for this purpose the query described above.

c# mysql sql database

  • In your products table you keep the text of the Category or Idcatg? The correct would be the ID this way you will automatically have the products always updated.

  • Show how your table structure looks Produtos

  • I keep the Category. The structure is: Idproduct, Brand, Name, Category.

  • Can’t you change the structure to save the ID? This will save you a lot of headaches

  • At this stage I have no way. Is there any solution for it edit?

1 answer

1


There are two serious problems in its structure by not saving the IDCatg on the table Produtos.

  1. When your Category has changed its description it will become outdated in the table Produtos. (Your current situation)
  2. When you update the category description in the table Produtos you don’t know if that description belongs to IDCatg you want to change. Then you may happen to update the different Idcatg Category.

-

If you want to go on anyway:

UPDATE Produtos, Categorias
SET Produtos.Categoria = @Categoria,
    Categorias.Categoria = @Categoria
WHERE Produtos.Categoria = Produtos.Categoria
  AND Categorias.IDCatg = @IDCatg

  • Thank you so much for your help. There will be no problem because the category in the products is not edited by hand, but by dropdownlist that queries the table Categories.

  • @Chiraggeiantilal All right, so if you solved your problem please check as the solution. :)

  • There is a syntax error between Products p and SET.

  • Removes the alias p and the p. , keeps giving error?

  • This way? UPDATE Products SET Category = @Category WHERE Category IN (SELECT c.Category FROM Categories c WHERE c.Idcatg = @Idcatg)

  • @Chiraggeiantilal That doesn’t work?

  • Works, but only edits the product category, does not edit the category of the categories table. Another situation is if you have a category that is not defined in a product, you cannot edit.

  • As I said in the answer is to run two update, you want to do everything in 1 only?

  • Yeah, all in the same query. Something like this: if the category is being used in some product, it executes (the query below is not updating the table categories, just update the line Product category) "UPDATE Products SET Category = . @Category WHERE Category in (SELECT c.Category FROM Categories c WHERE c.Idcatg = -@Idcatg)" If Category is not in use in any product, run "UPDATE Categories set Category = @Category WHERE Idcatg =-@Idcatg"

  • @Chiraggeiantilal I updated my answer with your need, take a test :)

  • Thanks for your solution. You are giving incorrect syntax near Products, near SET and Products.Categoria.

  • @Chiraggeiantilal Check if the tables and fields are correct in my answer

  • It’s all right, yes.

  • 1

    It was ok, it was enough: "UPDATE Products SET Category = . @Category WHERE Category IN (SELECT c.Category FROM Categories c WHERE c.Idcatg = .@Idcatg)" "UPDATE Categories set Category = . @Category WHERE Idcatg = . @Idcatg" .

Show 9 more comments

Browser other questions tagged c# mysql sql database

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