• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • 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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

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