Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

update same table from another database

Posted on 2013-06-11
6
Medium Priority
?
362 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 664 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 1336 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 1336 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

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.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

618 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