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
The MySQL Extension Is Deprecated and Will Be Removed in the Future: Use MySQLi or Pdo Instead
Can't Connect to MySQL Server Error 111
How to Request a Random Row in Sql
Simple Way to Transpose Columns and Rows in Sql
How to Change MySQL Table Names in Linux Server to Be Case Insensitive
Dplyr Left_Join by Less Than, Greater Than Condition
Gem Install: Failed to Build Gem Native Extension (Can't Find Header Files)
Activerecord Arel or Condition
How to Delete Duplicate Records in MySQL Database
Error When Trying to Install App With MySQL2 Gem
How to Select Rows With Max(Column Value), Partition by Another Column in MySQL
How to Handle a Single Quote in Oracle Sql
Difference Between Having and Where in Sql
How to Insert a Value That Contains an Apostrophe (Single Quote)