MySQL Update Column With Value from Another Table

mysql update column with value from another table

In addition to this answer if you need to change tableB.value according to tableA.value dynamically you can do for example:

UPDATE tableB
INNER JOIN tableA ON tableB.name = tableA.name
SET tableB.value = IF(tableA.value > 0, tableA.value, tableB.value)
WHERE tableA.name = 'Joe'

Update column from another table column value

Your update join syntax is wrong, try following:

UPDATE comments
INNER JOIN comment_users ON comment_users.comment_id = comments.id
SET comments.user_id = comment_users.user_id

Update column with value from another table

You have a horrible data model. And in MySQL, + only means addition. You can use find_in_set():

UPDATE table1 t1 INNER JOIN 
table2 t2
ON find_in_set(t2.node_id, t1.node)
SET t1.name = t2.name;

Note you should spend your efforts fixing table1 (one row per node rather than in a string). Don't spend your time trying to make sense of the data model.

Update column value of another table in After Insert Trigger

It's a safety measure from MySql to avoid deadlocks.

But you can avoid it by not directly selecting from the table that will be updated via the trigger.

For example by using variables.

CREATE TRIGGER TrgOrderItemAftIns
AFTER INSERT ON ORDERITEM
FOR EACH ROW
BEGIN
INSERT INTO DEBUG_TABLE (MSG) VALUES (CONCAT('TrgOrderItemAftIns: ', NEW.ITEMID,',',NEW.PIECES));
UPDATE ITEM SET PIECES = PIECES - NEW.PIECES WHERE ID = NEW.ITEMID;
END;
SET @OrderID = (select ID from TabOrder where Name = 'Order 1');
SET @ItemID = (select ID from ITEM where Name = 'Item1');
INSERT INTO ORDERITEM (OrderID, ITEMID, PIECES) VALUES
(@OrderID, @ItemID, 5);

SET @OrderID = (select ID from TabOrder where Name = 'Order 1');
SET @ItemID = (select ID from ITEM where Name = 'Item2');
INSERT INTO ORDERITEM (OrderID, ITEMID, PIECES) VALUES
(@OrderID, @ItemID, 1);

SET @OrderID = (select ID from TabOrder where Name = 'Order 2');
SET @ItemID = (select ID from ITEM where Name = 'Item1');
INSERT INTO ORDERITEM (OrderID, ITEMID, PIECES) VALUES
(@OrderID, @ItemID, 3);

Demo on db<>fiddle here

Mysql update column with value if id is in another table

Your first query should work if you use the right JOIN conditions. TRable aliases make it easier to follow:

UPDATE oclh_product p INNER JOIN 
oclh_product_to_category pc
ON pc.product_id = p.product_id
SET p.mpn = '99999'
WHERE pc.category_id = 26;

If you are storing a number in mpn, why is the type a string? It is better to use a number type of some sort.

Update column with part of the data from column of another table in another database with intermediate tables

I have found the solution. The problem was quite complex but the answer turned out to be simple after thinking it through.

My update statement that almost worked was lacking an additional condition in select statement and where clause.

update DB1.Tab1 as T1,
(select
col3, T1.col2 as T1c2
from
DB2.Tab2 as T2
inner join DB2.Tab4 as T4 ON T2.col5 = T4.col8
inner join DB1.Tab3 as T3 ON T4.col9 = T3.col6
inner join DB1.Tab1 as T1 ON T3.col7 = T1.col2
where
T1.col1 is null
and T1.col2 is not null
group by col3
having count(*) = 1) as T2d
set
T1.col1 = T2d.col3
where
T1.col1 is null
and T1.col2 = T1c2;

The solution comes down to selecting yet another column from the table to be updated (T1.col2), and specifically the values for which T1.col1 should be updated, then compare each T1.col2 with the previously selected ones.

However the mechanism behind it isn't clear to me, specifically why update statement without this edit would update all fields with only one value, so comments are still appreciated.

Update MySQL table with smallest value from another table

You could use a sub query:

update t1
inner join (select ID, min(Value) as minimum from t2 group by ID) tempt2 on t1.ID=tempt2.ID
set t1.value=tempt2.minimum;

Basically, you're looking up that minimum value in the second table for each ID, you call that table tempt2, and you join on that.

Update one column of a table with column of another table (one to many relationship)

First, you should probably be looking up the date using a join in a select query, rather than duplicating the data across tables. Let me assume that you have a good reason for the data duplication.

Second, your query should work, but it would be better written using explicit join syntax:

UPDATE TableB b JOIN
TableA a
USING (in_number)
SET b.anchor_date = a.date ;

Update a column from a SELECT of another table

You should use a JOIN rather using the subquery as a value.

UPDATE Dayfile AS d
JOIN (
SELECT DATE(LogDateTime) AS date, MAX(FeelsLike) AS feels
FROM Monthly
GROUP BY date
) AS m ON DATE(d.LogDate) = m.date
SET d.MaxFeelsLike = m.feels

Update column value of one table based on values from another table

In your query, table_y comes from nowhere.
Try this:

UPDATE table_x x
JOIN table_y y
ON x.latitude BETWEEN y.min_latitude AND y.max_latitude
SET x.location = y.location


Related Topics



Leave a reply



Submit