7
Update a table based on the result of a join in MySQL
source link: https://www.codesd.com/item/update-a-table-based-on-the-result-of-a-join-in-mysql.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Update a table based on the result of a join in MySQL
advertisements
I understand the syntax here:
UPDATE table
SET column1 = expression1,
column2 = expression2,
...
WHERE conditions;
and here:
UPDATE table1
SET column1 = (SELECT expression1
FROM table2
WHERE conditions)
WHERE conditions;
...but what if I want to update
UPDATE table1
SET column1 = expression
if we get a particular result on a join between table1 and table2
which has WHERE conditions?
I can't seem to figure it out, and I get syntax errors in all of my attempts. Any advice greatly appreciated.
The syntax in MySQL is:
UPDATE table1 JOIN
table2
ON conditions
SET table1.column1 = table2.expression1
WHERE conditions;
Related Articles
Update a table based on the count values of another table
I'm using MS Access VBA to update a table in the database file based on the count values on another table, which references back to the table I want to update. Table 1: request - Columns: requestID, requestStatus Table 2: requestItem - Columns: reque
Dynamically create a temporary table based on the results of SP
I have a SP that calls another SP and the resultset needs to be filtered to only the columns that I am interested in. DECLARE @myTable TABLE ( Field1 INT, Field2 INT, Field3 INT ) --If someSP returns say 30 params and I need only 3 params, I don't wa
Update another table based on the last record
I have the below table (sorry couldn't figure out how to post a table... in bold are the field names) code desc channel date 1001 A supermarket 10-oct 1001 B minimarket 15-dic 1003 A restaurant 07-may 1003 B bar 30-abr 1003 A restaurant 12-dic 1002 B
How to update a table based on the generated index key of an insert?
I'm created a temp table with most of the values I need to insert into a set of tables. From this temp table I have all the values I need for the insert to the first table, but the insert to the next table depends on the identity key generated by the
How to write a trigger that updates a table based on the action taken in another table?
I have a table named ORDERITEMS. CREATE TABLE DB2ADMIN.ORDERITEMS ( ORDERITEMS_ID BIGINT NOT NULL, STOREENT_ID INTEGER NOT NULL, ORDERS_ID BIGINT NOT NULL, TERMCOND_ID BIGINT, TRADING_ID BIGINT, ITEMSPC_ID BIGINT, CATENTRY_ID BIGINT, PARTNUM VARCHAR(
How to get the results of a database table based on the results of a different table in cakephp?
For example I have the following tables in my database: People Groups And in the People table I have the following columns: Name Birthdate Group In the Groups table I have the following columns: Name Colour Now I want to fetch results from my databas
How to run a query on a table based on the results of a query on another table?
I'm not sure that a normal join will help me in the situation I have: I have two tables as part of an app I'm building: one just lists a username in the first column and a friend's username in the second column. This table is strictly for keeping tra
updating a SQL table based on the results of a sub-selection
When I run this SQL code I get the following error message: Msg 116, Level 16, State 1, Line 17 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. What I want is whenever anything is returned from
Combine two tables and create a new column in the second table based on the results
This question already has an answer here: Not able to formulate query to combine different row values in single row using pivot table 1 answer I have two temp table, from that want to combine both of them like following.Is there any way to do this? I
How to update a table based on the count value of another table when matching IDs?
There are two tables: articles and comments. There is an association between them: each comment holds the article id it belongs to. ----------------------------- articles ----+-------+---------------- id | title | comments_count ----+-------+--------
Select the rows of one table based on the results of two other tables in mySQL using PDO
I have structured my Database in 3 tables: members - membersitems - items --------------------------------------------------------- memberid - memberitemid - itemid username - itemid - itemname password - memberid - itemtype etc The idea is that ever
Select in a table based on select results from another table
I'm trying to return rows from one table, based on the results of a select from another table, but I'm not well versed in SQL/Oracle and am struggling to link the two together. ITEMS DEFS item_id (1000) trans (1125) item_state (Y) desp (680) trans (1
How to make the results of a join statement a new table
I have the following mysql query. How can i make the result of the query be a new table; SELECT t1.referree AS LEVEL1, t2.referree AS LEVEL2, t3.referree AS LEVEL3, t4.referree AS LEVEL4, t5.referree AS LEVEL5, t6.referree AS LEVEL6, t7.referree AS L
UPDATE table based on the query
I have not used UPDATE before and I am trying to update a table based on a query of multiple tables. What I am trying to do is change the servicetype.servicetype_id from 1 to 74571 on the services returned in my query. When I execute the query below
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK