使用LEFT JOIN在多个表上使用INSERT into语句？ [英] Using INSERT into statement on multiple tables with LEFT JOIN?

问题描述

你好朋友，



我是一个SQL新手，我必须在我们的客户维修数据库中更改一些东西。



这就是我所拥有的：



有一个REPAIR表，它有一个STATUS列，其中1表示不修复完成，2表示修复完成。

还有一个ITEM表，其中1表示已完成，2表示未完成。



不幸的是，员工犯了一些错误，现在 ITEM 表中的项目 ITEMSTATUS1艰难的 ITEMSTATUS 应设置为2，因为所属的修复已设置为2（已完成）。



我尝试了什么：



这是我写的一个sql查询给我看的全部状态应设置为2的项目：

Hello friends,

I'm an SQL newbie and I have to change few things in our customer-repair-database.

Here is what I have:

There is a REPAIR table and it has a STATUS column, where "1" means repair not done, and "2" means repair is done.
There is also an ITEM table with the column ITEMSTATUS, where "1" also means done, and "2" means not done.

Unfortunately, there were some mistakes made by emplyees and now there are items in the ITEM table with ITEMSTATUS "1" even tough ITEMSTATUS should be set to "2" because the belonging repair is already set to "2" (done).

What I have tried:

Here is an sql query I wrote to show me all items where the status sould be set to "2":

SELECT REPAIR.REPAIRID,
REPAIR.STATUS,
ITEM.REPAIRID,
ITEM.ITEMID,
ITEM.ITEMSTATUS
FROM REPAIR
LEFT JOIN ITEM ON REPAIR.REPAIRID = ITEM.REPAIRID
WHERE REPAIR.STATUS = '2' AND ITEM.ITEMSTATUS = '1'

这个sql查询显示了我需要将ITEMSTATUS设置为2的所有项目。由于显示了超过2000个项目，我想避免手动执行:)。

现在我不知道如何使用描述查询的INSERT INTO语句，我希望有人可以帮助我。

This sql query shows me all the items where I need to set ITEMSTATUS to "2". Since there are over 2000 items shown I'd like to avoid doing that manually :).
Now I don't know how to use the INSERT INTO statement with the described query and I hope someone could help me.

推荐答案

你不想做一个INSERT而是一个UPDATE。你的sql就在那里。

You do not want to do an INSERT but rather an UPDATE. And your sql is just about there.

UPDATE i
SET itemstatus = '2'
FROM Item i
LEFT JOIN Repair r ON i.REPAIRID = r.REPAIRID
WHERE r.STATUS = '2' AND i.ITEMSTATUS = '1'

这篇关于使用LEFT JOIN在多个表上使用INSERT into语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！