Solved

update same table from another database

Posted on 2013-06-11
6
359 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 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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mysql sync between 3-4 mysql db 4 47
SELECT query on two levels (detail and summary) 13 64
MySQL 5.6.30 - daily outages 46 67
MySQL programmer starter 25 29
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 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