Update with nested select statements

Posted on 2007-03-29
Last Modified: 2012-05-05
I have an insert statement that works properly.  I am not well-versed by any stretch of the imagination when trying to update a field called 'transferred' inside my table (a_line2) using the same criteria below.  I would appreciate someone helping me out.  

Two additional notes:
1...I am having to nest these statements to insert rows in this instance because there are other rows with the same invoice_id, contact_id and property_id I don't want.  The accounting_type can be different.
2....there is a unique field called accounting_id in each of the files that could be referenced if necessary.

INSERT INTO a_line2 (transaction_id, accounting_type, invoice_id, contact_id, property_id)
SELECT a.transaction_id, a.accounting_type, a.invoice_id, a.contact_id, a.property_id
FROM accounting_line AS a INNER JOIN (SELECT accounting_type, invoice_id, contact_id, property_id
FROM accounting
WHERE (accounting_type <= 30)) AS b ON a.accounting_type = b.accounting_type AND a.invoice_id = b.invoice_id AND a.contact_id = b.contact_id AND a.property_id = b.property_id

Question by:TSFLLC
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 29

Expert Comment

ID: 18821534
You want to update a field based on a select/join?

update a_line2
set transferred = ???
accounting_line AS a INNER JOIN (SELECT accounting_type, invoice_id, contact_id, property_id
FROM accounting
WHERE (accounting_type <= 30)) AS b ON a.accounting_type = b.accounting_type AND a.invoice_id = b.invoice_id AND a.contact_id = b.contact_id AND a.property_id = b.property_id

although your where clause may be different for the update than it is for an insert.. this is your syntax
LVL 29

Expert Comment

ID: 18821561

update a_line2
set transferred = ???
from tablename
inner join tablename
where etc
LVL 50

Expert Comment

ID: 18821789
nope still confised...
can you provide some example data?

please specify the relationships between the 3 tables...

do you really want to update existing data on a_line2 or is it just to also put data into the transferred column
whilst inserting the data to a-Line2?

please also try to example what the business reason is behind the process.. that will help us as well.
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.


Author Comment

ID: 18823172
My mistake guys.  I had worked about a 16 hour day yesterday.  I meant to write that I am attempting to update a flag called (transferred) setting it to 1 in the table (accounting_line).  What I am attempting to do is copy all of the accounting_line rows into a temporary file a_line2, update the field transferred in the original file accounting_line and then run a delete statement against accounting_line where transferred = 1.  The overall purpose is to identify any orphaned invoice line rows against invoice headers and get them out of my tables.

Here is what I have been attempting to do....but it times out:

UPDATE accounting_line SET transferred = 1
FROM (SELECT DISTINCT accounting_type, invoice_id, contact_id, property_id
FROM accounting_line AS al WHERE (accounting_type <= 30)) AS b INNER JOIN
accounting AS acc ON b.accounting_type = acc.accounting_type AND b.invoice_id = acc.invoice_id AND b.contact_id = acc.contact_id AND b.property_id = acc.property_id
LVL 29

Accepted Solution

QPR earned 500 total points
ID: 18827651
that seems a strange way to go about things? why update only to then delete?
What about

delete from invoiceheadertable
where headerid not in
(select rowid from invoicerowtable)

This assumes that you have a primary/foreign key relationship between the 2 tables.


Author Comment

ID: 18830120

I was under a time crunch and accomplished my goal by unfortunately using a VB recordset to complete it.  This is my first SQL database and am soaking up more each day.

>> This assumes that you have a primary/foreign key relationship between the 2 tables.

This is not the case.  I have not set up ANY primary or foreign keys within any of my tables yet.  I decided I didn't want to screw things up, or better said, open up that can of worms yet.  Your code above, however, would have saved me alot of headache with this issue and several others associated with this whole process had I created the keys and also been aware of how you could use the syntax of your sql statement above.

If you can perform a delete like this I would assume you could perform a carefully worded select statement in a similar way?

Thanks for the help.
LVL 29

Expert Comment

ID: 18830850
no problem.
It's called a subquery or subselect
You can use it to create a virtual table and then select/update/delete based on it's contents.
There are probably other ways to achieve the same results - there is usually more than 1 way to achieve things in SQL.
If going forward with SQL I'd advise always having a key in your tables. Even if the key is not real data. In other words an id column purely to give you a guarenteed unique value in your rows (primary key).
Then when you build other tables (related) you can use this id in your second table as a "foreign key" so that you have a link between 2 tables pointing to related data. Once you begin building these relationships your DB becomes alot more useful and you can divide info up into smaller tables (normalisation).

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Sum Over Multiple Tables 20 35
Regarding Disk IO 3 49
Need help with a query 14 40
SSRS Page Header from Group Data 2 29
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question