Solved

Update with nested select statements

Posted on 2007-03-29
7
366 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

863 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

18 Experts available now in Live!

Get 1:1 Help Now