Updating multiple columns in mysql

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.Visit Stack Exchange Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. I have a table with 3 columns, id (Primary Key) and two unique Key column, column_1_id and column_2_id.A column_1_id (referenced of a user's ID) can exist many times in the DB, but must have a different column_2_id each time. I need to update rows with column_2_ID with either 2 or 4.The end goal is to have no rows exist that have a column_2_ID equal to either 2 or 4.So for example currently the below diagrams can, and do exist.

The row with column_2_ID = 4 should be deleted, and the row with column_2_ID = 2 updated to be column_2_ID = 3.

For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

If you set a column to the value it currently has, My SQL notices this and does not update it.

The proprietary syntax does not work all the time: CREATE TABLE Orders (order_nbr INTEGER NOT NULL PRIMARY KEY, some_col DECIMAL (9,2) NOT NULL); INSERT INTO Orders VALUES (1, 0); INSERT INTO Orders VALUES (2, 0); INSERT INTO Orders VALUES (3, 0); CREATE TABLE Order Details (order_nbr INTEGER NOT NULL, sku INTEGER NOT NULL, item_price DECIMAL (9,2) NOT NULL, PRIMARY KEY(order_nbr, sku), -- FOREIGN KEY(sku) REFERENCES Products(sku) FOREIGN KEY(order_nbr) REFERENCES Orders(order_nbr)); INSERT INTO Order Details VALUES (1, 1, 500.00); INSERT INTO Order Details VALUES (1, 2, 205.00); INSERT INTO Order Details VALUES (2, 1, 490.95); INSERT INTO Order Details VALUES (3, 1, 480.00); SELECT * FROM Orders; UPDATE Orders SET Orders.some_col = Order Details.item_price FROM Orders INNER JOIN Order Details ON Orders.order_nbr = Order Details.order_nbr; results -- see item #1; last physical value 1 205.00 - where is the 0.00? The ANSI equivalent of the incorrect query above is UPDATE Orders -- no alias allowed!

2 490.95 3 480.00 --repeat with new physical ordering DELETE FROM Order Details; DELETE FROM Orders; DROP INDEX Order Details.foobar; -- index will change the execution plan CREATE INDEX foobar ON Order Details (order_nbr, item_price); INSERT INTO Orders VALUES (1, 0); INSERT INTO Orders VALUES (2, 0); INSERT INTO Orders VALUES (3, 0); INSERT INTO Order Details VALUES (1, 2, 205.00); INSERT INTO Order Details VALUES (1, 1, 500.00); INSERT INTO Order Details VALUES (2, 1, 490.95); INSERT INTO Order Details VALUES (3, 1, 480.00); UPDATE Orders SET Orders.some_col = Order Details.item_price FROM Orders INNER JOIN Order Details ON Orders.order_nbr = Order Details.order_nbr; SELECT * FROM Orders; Results 1 500.00 2 490.95 3 480.00 What is the first property that you must have in an INDEX? SET some_col = (SELECT item_price FROM Order Details WHERE Order Details.order_nbr = Orders.order_nbr) WHERE EXISTS (SELECT * FROM Order Details WHERE Order Details.order_nbr = Orders.order_nbr); This will of course result in an error, and even the most junior of junior programmers will eventually figure out (probably by asking a senior) what's wrong.

Search for updating multiple columns in mysql:

updating multiple columns in mysql-1updating multiple columns in mysql-55

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “updating multiple columns in mysql”