• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

SQL Question - Need to do some kind of batch update from one table to another based on onlly one condition.

I have just created and released a new sale view in my database. I have already moved all my data into the new table, but one of my users decided it was a good idea to use an old field for data that wasn't suposed to go there. Needless to say I now need to move just the data in the one old field for her into the ne wtable. Luckily all the IDs are the same so this should just be a matter of getting the statement down with the right join info.

I'm guessing I would need "something" like below though I don't know too much about joing with updates:

UPDATE NEWSALE A1 SET A1.DESIGNNAME = A2.DESIGNNOTES WHERE A1.ACCOUNTMANAGER='Sales Persons ID' INNER JOIN OLDSALE A2 ON (A1.SALEID=A2.SALEID)
0
netadmin2004
Asked:
netadmin2004
  • 3
  • 2
1 Solution
 
UnifiedISCommented:
UPDATE NEWSALE SET DESIGNNAME = DESIGNNOTES FROM OLDSALE A2 INNER JOIN NEWSALE A1 ON A2.SALEID = A1.SALEID AND A1.ACCOUNTMANAGER = 'Sales Persons ID'
0
 
netadmin2004Author Commented:
Thanks that is exactly what I needed from what I wrote however I completely forgot I don't have the accountmanagerid in both sales tables. Crap!

I do have a contact table that has the accountmanagerid and the contactid fields are in both sale table. Is it possible to do some type of three way join? I would have no idea how to get what I need done otherwise :(.
0
 
netadmin2004Author Commented:
Is it possible to create a join using 3 tables to get the info I need? If not is there some other option I should use that you could reccomend?
0
 
UnifiedISCommented:
Sure, just add another inner join for the third table
UPDATE NEWSALE SET DESIGNNAME = DESIGNNOTES FROM OLDSALE A2 INNER JOIN NEWSALE A1 ON A2.SALEID = A1.SALEID
INNER JOIN ThirdTable A3
ON A1.fk = A3.fk
AND A2.ACCOUNTMANAGER = A3.ACCOUNTMANAGER

You're joining the table based on multiple conditions, you just need the third table to link the other 2
0
 
netadmin2004Author Commented:
Awesome!!! Here's what my statement ended up looking like:

UPDATE PP_SALES SET DESIGNNAME = DESIGNNOTES FROM SALE A2 INNER JOIN PP_SALES A1 ON A2.SALEID = A1.PP_SALESID
INNER JOIN CONTACT A3
ON A1.CONTACTID = A3.CONTACTID
WHERE A3.ACCOUNTMANAGERID = 'U6UJ9A000007'

Thanks so much!
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now