基于选择查询的更新查询

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:

到目前为止，我所拥有的是:

SELECT name as name_A, date-time as end_DTS, id as id_A 
FROM tableA WHERE criteria = 1


SELECT name as name_B, date-time as start_DTS, id as id_B 
FROM tableA WHERE criteria = 2

Then I join them:

然后我加入他们:

SELECT name_A, name_B, id_A, id_B, 
if(start_DTS > end_DTS,'VALID','') as validation_check
FROM tableA
LEFT JOIN tableB ON name_A = name_B

Can I then, based on my validation_check field, run a UPDATE query with the SELECT nested?

然后，我可以基于validation_check字段运行带有SELECT嵌套的更新查询吗?

11 个解决方案

#1

601

You can actually do this one of two ways:

你可以用以下两种方法来做:

MySQL update join syntax:

MySQL更新连接的语法:

update tableA a
left join tableB b on a.name_a = b.name_b
set validation_check = if(start_dts > end_dts, 'VALID', '')
-- where clause can go here

ANSI SQL syntax:

ANSI SQL语法:

update tableA set validation_check = 
    (SELECT if(start_DTS > end_DTS,'VALID','') as validation_check
        FROM tableA
        LEFT JOIN tableB ON name_A = name_B
        WHERE id_A = tableA.id_A)

Pick whichever one seems most natural to you.

挑一个你觉得最自然的。

#2

214

UPDATE table1 dest, (SELECT * FROM table2 where id=x) src 
  SET dest.col1 = src.col1 where dest.id=x ;

Hope this works for you.

希望这对你有用。

#3

Easy in MySQL:

容易在MySQL中:

UPDATE users AS U1, users AS U2 
SET U1.name_one = U2.name_colX
WHERE U2.user_id = U1.user_id

#4

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:

这是一个更好和清洁的各级:

update dbname1.content targetTable

left join dbname2.someothertable sourceTable on
    targetTable.compare_field= sourceTable.compare_field
set
    targetTable.col1  = sourceTable.cola
    ,targetTable.col2 = sourceTable.colb 
    ,targetTable.col3 = sourceTable.colc 
    ,targetTable.col4 = sourceTable.cold

Traaa! It works great!

Traaa !它太棒了!

With 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.

有了以上的理解，您可以修改set字段和“on”条件来完成您的工作。您还可以执行检查，然后将数据提取到临时表中，然后使用上面的语法替换表和列名来运行更新。

Hope it works, if not let me know. I will write an exact query for you.

如果不让我知道，希望它能成功。我将为您写一个确切的查询。

#5

    UPDATE 
  receipt_invoices dest,
  (SELECT 
    `receipt_id`,
    CAST((net * 100) / 112 AS DECIMAL (11, 2)) witoutvat 
  FROM
    receipt 
  WHERE CAST((net * 100) / 112 AS DECIMAL (11, 2)) != total 
    AND vat_percentage = 12) src 
SET
  dest.price = src.witoutvat,
  dest.amount = src.witoutvat 
WHERE col_tobefixed = 1 
  AND dest.`receipt_id` = src.receipt_id ;

Hope this will help you in a case where you have to match and update between two tables.

希望这将有助于您在两个表之间进行匹配和更新。

#6

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.

我需要填充activities表中的product_id字段，其中活动在一个单元中编号，单元在一个级别中编号(使用字符串??N标识)，这样就可以使用SKU ie L1U1A1标识活动。然后将这些sku存储在另一个表中。

I identified the following to get a list of activity_id vs product_id:-

我标识了以下内容以获取activity_id与product_id:-的列表

SELECT a.activity_id, w.product_id 
FROM activities a 
JOIN units USING(unit_id) 
JOIN product_types USING(product_type_id) 
JOIN web_products w 
  ON sku=CONCAT('L',SUBSTR(product_type_code,3), 'U',unit_index, 'A',activity_index)

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:-

我发现这太复杂了，无法合并到mysql中的SELECT中，所以我创建了一个临时表，并将其与update语句结合:-

CREATE TEMPORARY TABLE activity_product_ids AS (<the above select statement>);

UPDATE activities a JOIN activity_product_ids b ON a.activity_id=b.activity_id 
  SET a.product_id=b.product_id;

I hope someone finds this useful

我希望有人觉得这有用

#7

You can update values from another table using inner join like this

您可以使用内部联接来更新来自另一个表的值。

UPDATE [table1_name] AS t1 INNER JOIN [table2_name] AS t2 ON t1.column1_name] = t2.[column1_name] SET t1.[column2_name] = t2.column2_name];

Follow here to know how to use this query http://www.voidtricks.com/mysql-inner-join-update/

下面了解如何使用这个查询http://www.void涓涓/mysql- inside -join-update/

or you can use select as subquery to do this

也可以使用select as子查询来实现这一点

UPDATE [table_name] SET [column_name] = (SELECT [column_name] FROM [table_name] WHERE [column_name] = [value]) WHERE [column_name] = [value];

query explained in details here http://www.voidtricks.com/mysql-update-from-select/

查询详细解释如下:http://www.void涓涓/mysql-update-from-select/

#8

UPDATE [table_name] AS T1,
      (SELECT [column_name] 
        FROM [table_name] 
        WHERE [column_name] = [value]) AS T2 
  SET T1.[column_name]=T2.[column_name] + 1
WHERE T1.[column_name] = [value];

#9

You can use:

您可以使用:

UPDATE Station AS st1, StationOld AS st2
   SET st1.already_used = 1
 WHERE st1.code = st2.code

#10

For same table,

对于同一个表,

UPDATE PHA_BILL_SEGMENT AS PHA,
     (SELECT BILL_ID, COUNT(REGISTRATION_NUMBER) AS REG 
       FROM PHA_BILL_SEGMENT
        GROUP BY REGISTRATION_NUMBER, BILL_DATE, BILL_AMOUNT
        HAVING REG > 1) T
    SET PHA.BILL_DATE = PHA.BILL_DATE + 2
 WHERE PHA.BILL_ID = T.BILL_ID;

#11

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.

我在一个表中有一个条目重复的问题。下面是我的方法。@sibaz也提倡这样做。

Finally I solved it using the below queries:

最后我用下面的查询解决了这个问题:

The select query is saved in a temp table

select查询保存在临时表中

IF OBJECT_ID(N'tempdb..#New_format_donor_temp', N'U') IS NOT NULL
    DROP TABLE #New_format_donor_temp;

select *
into #New_format_donor_temp
from DONOR_EMPLOYMENTS
where DONOR_ID IN (
  1, 2
)

-- Test New_format_donor_temp
-- SELECT *
-- FROM #New_format_donor_temp;

The temp table is joined in the update query.

在更新查询中加入了临时表。

UPDATE de
SET STATUS_CD=de_new.STATUS_CD, STATUS_REASON_CD=de_new.STATUS_REASON_CD, TYPE_CD=de_new.TYPE_CD
FROM DONOR_EMPLOYMENTS AS de
  INNER JOIN #New_format_donor_temp AS de_new ON de_new.EMP_NO = de.EMP_NO
WHERE
  de.DONOR_ID IN (
    3, 4
)

I not very experienced with SQL please advise any better approach you know.

我对SQL不是很熟悉，请建议更好的方法。

Above queries are for MySql server.

以上查询是针对MySql服务器的。

#1

601