Using INSERT into statement on multiple tables with LEFT JOIN?

Question

0.00/5 (No votes)

See more:

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

SQL

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'

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.

Posted 24-May-17 4:30am

Vincent Warte

Updated 24-May-17 7:25am

Add a Solution

Comments

Michael_Davies 24-May-17 11:17am

Do you mean you want to update the item.itemstatus where the repair.status=2?

A lot depends on the version of SQL you are using;

https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql

Vincent Warte 26-May-17 5:38am

Thank you, that is helpful for the future.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2017-05-24T07:24:00

Solution 1

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

SQL

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'

Posted 24-May-17 7:24am

ZurdoDev

Comments

Vincent Warte 26-May-17 5:37am

Thank you very much, that is exactly what I was looking for.

ZurdoDev 26-May-17 7:02am

You're welcome.

Using INSERT into statement on multiple tables with LEFT JOIN?

1 solution

Solution 1

Add your solution here

Preview 0

Using INSERT into statement on multiple tables with LEFT JOIN?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us