Solved

update same table from another database

Posted on 2013-06-11
6
360 Views
Last Modified: 2013-06-23
update db1.table1 set varcharCol=null
where db2.table1.varcharCol=null
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 25

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 60

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 25

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

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

will this solution work for mysql
0
 
LVL 60

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

707 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