Solved

Update with nested select statements

Posted on 2007-03-29
7
368 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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
Database ERD 4 25
SQL query and VBA 5 45
VB.net Duplicating a table - primary key not created 3 31
changing page verifacation 1 23
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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

809 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