Solved

update same table from another database

Posted on 2013-06-11
6
357 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to Migrate from SQL to MYSQL in .net Web Application 2 50
PHP - modify class to use temporary MySQL table? 15 53
Database Design Concept 3 50
html input clean up 3 47
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Creating and Managing Databases with phpMyAdmin in cPanel.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

919 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