Solved

update same table from another database

Posted on 2013-06-11
6
358 Views
Last Modified: 2013-06-23
update db1.table1 set varcharCol=null
where db2.table1.varcharCol=null
0
Comment
Question by:rgb192
  • 2
  • 2
  • 2
6 Comments
 
LVL 24

Assisted Solution

by:chaau
chaau earned 166 total points
ID: 39239797
You would normally write: db1.dbo.table1, db2.dbo.table2
(if the owner of the tables is dbo)

However, this only applies to the databases on the same server of the same database instance. If you are trying to join with a table on a different server, you would need to create a link to this server. When the link is established, you access the tables like this:
server.db1.dbo.table1. Please note, that when you have a multi-instance server, you would have server\instance1.db1.dbo.table1
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 334 total points
ID: 39240795
@chaau, the question says MySQL. @rgb192, please advise if you are using MS SQL instead.

Otherwise, if you are using MySQL, UPDATE with JOIN syntax is like this:

UPDATE table1 t1
JOIN table2 t2 ON t1.key_column = t2.key_column
SET t1.varcharCol = NULL
WHERE t2.varcharCol IS NULL
;

Open in new window


If the table is in another database, it is the same issue really as updating one table from another. It just becomes a notation change. e.g., table1 is same as db1.table1.

In addition, note that you will want to JOIN the tables on some common field, such as key_column in my example, or you will update ALL rows in your target table to NULL as long as there exists a row in the look-up table that has a NULL value.

I hope that helps!

Kevin
0
 
LVL 24

Expert Comment

by:chaau
ID: 39240821
Thanks mwvisa. I have noticed that after I have written my post. You are right, for MySQL server the syntax will be slightly different
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:rgb192
ID: 39241618
I am using mysql not microsoft sql

will this solution work for mysql
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 334 total points
ID: 39241789
http:Q_28154524.html#a39240795 is for MySQL regardless of if the other table is in the same database/schema or a different one on the same MySQL server. The key is the syntax for UPDATE with JOIN. Another way to do this is as follows.

UPDATE t1, t2 ... WHERE t1.key_column = t2.key_column

The gist is the same, though, the JOIN happens in the UPDATE portion of the statement unlike MS SQL where it happens in the FROM.
0
 

Author Closing Comment

by:rgb192
ID: 39270436
this updates one table with values from another, thanks
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

Suggested Solutions

Title # Comments Views Activity
move records one table to another 14 64
MySQL - Limit or Top Records 15 49
CheckListBox usage 3 59
configure dependency in POM for new database 3 25
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

777 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