Update with nested select statements

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!
TSFLLCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QPRCommented:
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
QPRCommented:
correction...

update a_line2
set transferred = ???
from tablename
inner join tablename
where etc
0
LowfatspreadCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TSFLLCAuthor Commented:
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
QPRCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TSFLLCAuthor Commented:
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
QPRCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.