Mysql - How to use UPDATE with SELECT???
The following examples forces MySQL to use the employee_emp_nm_idx for this query. Mysql> SELECT * FROM employee FORCE INDEX (EMPLOYEE_EMP_NM_IDX) WHERE NAME LIKE 'JOHN%'; To display all available indexes on a particular table, use the “show index” command. The following example displays all indexes available on employee table.
I have two tables:
1) products_category
-cat_id
-cat_name
1) products_category
-cat_id
-cat_name
2) related_category
-rel_cat_id (exaclty same as products_category.cat_idrelated_category.rel_cat_id)
-rel_cat_name
-rel_cat_id (exaclty same as products_category.cat_idrelated_category.rel_cat_id)
-rel_cat_name
products_category table
[IMG]http://www.ephpsolutions.com/scud/rrr/product_category.gif[/IMG]
[IMG]http://www.ephpsolutions.com/scud/rrr/product_category.gif[/IMG]
related_category table
[IMG]http://www.ephpsolutions.com/scud/rrr/related_category.gif[/IMG]
I want to get cat_name from products_category and want to store in rel_cat_name in related_category tabel. and the query should be only one.
Not sure how will it works.
I thought it would be something like:
I thought it would be something like:
But it doesn't works,
Please assist. Thanks in advance
Please assist. Thanks in advance
- 2 Contributors
- forum2 Replies
- 21,429 Views
- 7 Hours Discussion Span
- commentLatest Postby rahulephp
![Sample sql queries Sample sql queries](/uploads/1/2/6/3/126392018/107719941.png)
Active2 months ago
I need to check (from the same table) if there is an association between two events based on date-time.
One set of data will contain the ending date-time of certain events and the other set of data will contain the starting date-time for other events.
If the first event completes before the second event then I would like to link them up.
What I have so far is:
Then I join them:
Can I then, based on my validation_check field, run a UPDATE query with the SELECT nested?
dreftymac17k21 gold badges94 silver badges156 bronze badges
John MJohn M6,55228 gold badges79 silver badges129 bronze badges
12 Answers
You can actually do this one of two ways:
MySQL update join syntax:
ANSI SQL syntax:
Pick whichever one seems most natural to you.
Valerian Pereira5971 gold badge5 silver badges13 bronze badges
EricEric75k9 gold badges106 silver badges112 bronze badges
automatix3,68016 gold badges57 silver badges155 bronze badges
massquotemassquote2,8891 gold badge9 silver badges13 bronze badges
Lucky11.6k13 gold badges86 silver badges124 bronze badges
SergioSergio1,4771 gold badge8 silver badges18 bronze badges
If somebody is seeking to update data from one database to another no matter which table they are targeting, there must be some criteria to do it.
This one is better and clean for all levels:
Traaa! It works great!
With the above understanding, you can modify the set fields and 'on' criteria to do your work. You can also perform the checks, then pull the data into the temp table(s) and then run the update using the above syntax replacing your table and column names.
Hope it works, if not let me know. I will write an exact query for you.
Valerian Pereira5971 gold badge5 silver badges13 bronze badges
KMXKMX
Hope this will help you in a case where you have to match and update between two tables.
DonDon
I found this question in looking for my own solution to a very complex join. This is an alternative solution, to a more complex version of the problem, which I thought might be useful.
I needed to populate the product_id field in the activities table, where activities are numbered in a unit, and units are numbered in a level (identified using a string ??N), such that one can identify activities using an SKU ie L1U1A1. Those SKUs are then stored in a different table.
I identified the following to get a list of activity_id vs product_id:-
I found that that was too complex to incorporate into a SELECT within mysql, so I created a temporary table, and joined that with the update statement:-
I hope someone finds this useful
sibazsibaz
Sample Sql Queries
bhavikbhavik
You can update values from another table using inner join like this
Follow here to know how to use this query http://www.voidtricks.com/mysql-inner-join-update/
or you can use select as subquery to do this
query explained in details here http://www.voidtricks.com/mysql-update-from-select/
Anand RoshanAnand Roshan
SergeyUrSergeyUr
Gnanam pragasamGnanam pragasam
I had an issue with duplicate entries in one table itself. Below is the approaches were working for me. It has also been advocated by @sibaz.
Finally I solved it using the below queries:
- The select query is saved in a temp table
- The temp table is joined in the update query.
I not very experienced with SQL please advise any better approach you know.
Above queries are for MySql server.
RickRick8381 gold badge15 silver badges24 bronze badges
Check with below query.
update tableA Ainner join tableB B on A.name_a = B.name_bset validation_check = if(start_dts > end_dts, 'VALID', ')
Select For Update Mysql Example Query
Anoop KumarAnoop Kumar