Click here to Skip to main content
15,509,322 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi, can someone help me with writing insert, update, and delete with inner join? maybe a example will help. I have to tables tblSystem and tblVendor with relationships sys_venID = venID. I'm able to SELECT inner join just fine but how do I incorporate that with insert, update and delete.

What I have tried:

Looking all over internet. Have not found anything.
Posted
Updated 7-Jul-22 4:15am
Comments
Richard MacCutchan 4-Jul-22 13:22pm    
You cannot (AFAIK) use JOINs on those commands. You need to explain how you think it would help with what you are trying to do.
Kong Lee 2022 4-Jul-22 14:13pm    
I want to be able to insert sysName, venName, sysDesc with out having to enter in sys_venID or displaying it. If that makes any sense.
PIEBALDconsult 4-Jul-22 15:02pm    
Generally better not to, so I won't tell you.

In addition to the comments and the other solution. It's not been made clear why it is "better not to". You've also tagged this C# which led to solution 1, but this is something you could do at your database level. I will have to assume MSSQL for this for now and I used this sample demo data
SQL
create table #a (id int, datum varchar(10));
insert into #a (id, datum) values
(1,'item 1'),
(2,'item 2');

create table #b (id int identity(1,1), datum varchar(10));
insert into #b (datum) values ('A'),('B'),('C'),('D');
You are probably using an INNER JOIN to filter your data in some way.

Firstly - Delete cannot be done. If you try something like
SQL
DELETE FROM #a
INNER JOIN #b on #a.id = #b.id
you will get a syntax error "Incorrect syntax near the keyword 'INNER'."

Read - you already know how to do this
SQL
SELECT #a.id, #a.datum, #b.datum
FROM #a
INNER JOIN #b on #a.id = #b.id;
Create - you could do something like this
SQL
INSERT INTO #a
SELECT #b.*
FROM #a
INNER JOIN #b on #a.id = #b.id;
BUT you will just end up with duplicates of the rows that are already in #a.
Most people would use a join here to insert rows that are on table #b but NOT on table #a e.g.
SQL
INSERT INTO #a
SELECT #b.id, #b.datum
FROM #b
LEFT OUTER JOIN #a on #a.id = #b.id
WHERE #a.id IS NULL;
Finally UPDATE, could look something like this
SQL
UPDATE #b
SET datum = #a.datum
FROM #a
INNER JOIN #b on #a.id = #b.id
although I have come across instances where this would not work unless you used a table alias e.g.
SQL
UPDATE b
SET datum = a.datum
FROM #a as a
INNER JOIN #b as b on a.id = b.id
 
Share this answer
 
CRUD is simple Create/Read/Update/Delete operations on a single table. The point at which you want to do a JOIN, you step firmly outside the CRUD paradigm. If you're using entity framework, you should probably use LINQ to do joins.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900