Solved

Update with nested select statements

Posted on 2007-03-29
7
365 Views
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

Thanks!
0
Comment
Question by:TSFLLC
  • 4
  • 2
7 Comments
 
LVL 29

Expert Comment

by:QPR
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
0
 
LVL 29

Expert Comment

by:QPR
ID: 18821561
correction...

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

Expert Comment

by:Lowfatspread
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.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:TSFLLC
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
0
 
LVL 29

Accepted Solution

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

0
 

Author Comment

by:TSFLLC
ID: 18830120
QPR,

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.
0
 
LVL 29

Expert Comment

by:QPR
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).
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now