Mysqlupdate multi-Table instance

Source: Internet
Author: User
Tags mysql update
For more information about mysqlupdate multi-Table instances, see Example 1: updatetab1settab1. item size (selecttab2. item size fromtab2wheretab2. product color tab1. product color) wheretabl1. the product color in (selecttab2. product color fromtab2) continues as follows: UPDATECMS

Mysql update multi-table example let's take a look at the following example: Example 1: update tab1 set tab1. commodity size = (select tab2. commodity size from tab2 where tab2. commodity color = tab1. commodity color) where tabl1. the product color in (select tab2. product color from tab2) continues as follows: UPDATE CMS

Mysql update multi-Table instances

Let's take a look at the following example:

Instance 1:

Update tab1 set tab1. item size = (select tab2. item size from tab2 where tab2. item color = tab1. item color) where tabl1. item color in (select tab2. item color from tab2)

Continue with the following example:

UPDATE CMS_Document A, CMS_Template B set a. RuleType2 = B. RuleType where a. TemplateID = B. TemplateID

An alternative method can be:

Update customers a -- use the alias set city_name = nvl (select B. city_name from tmp_cust_city B where B. customer_id = a. customer_id), a. city_name)

Or

Set city_name = nvl (select B. city_name from tmp_cust_city B where B. customer_id = a. customer_id), 'unknown ')

MySQL multi-Table update instance 3:

Update contact c, contactdroit cd set c. user_name = '$ username', c. nom = '$ lastname', c. prenom = '$ firstname', c. passcode = '$ password', cd. droit_id = '$ droitid' where c. contact_id = '$ id' and c. contact_id = cd. contact_id;

Mysql associates multiple tables for update

UPDATE Track inner join mv on Track. trkid = MV. mvid SET Track. is_show = MV. is_show WHERE trkid <6

Equivalent

UPDATE Track, mv set Track. is_show = MV. is_show WHERE Track. trkid = MV. mvid and trkid <6

Example 4:

UPDATE product p inner join productPrice pp ON p. productId = pp. productId SET pp. price = pp. price * 0.8, p. dateUpdate = CURDATE () WHERE p. dateCreated <'2017-01-01'

Note: More exciting articles, please pay attention to SanlianProgramming TutorialTopic.

,

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.