CODEWITHSUNDEEP
mysqlsqlstored-procedures
user1016265
user1016265

Reputation: 2387

MySQL procedure writing for data managing

I have db in MySQL, with two tables and I need to make query like this

SELECT a.*, b.* from db1.A a1 left join db2.A a2 using(id) where a1.email <> a2.email

So i would like to find guys who has non empty email field in db1.A table and their emails does not match the same guys email from db2.A, and write emails from db1.A to db2.A.

in the beginning we have

db1.A          db2.A
email          email
[email protected]      NULL

in result ii would like to get

db1.A          db2.A
email          email
[email protected]      [email protected]

I can make it with any script language, but can it be done (such tasks) with help only SQL ?

Upvotes: 1

Views: 39

Answers (4)

user1016265
user1016265

Reputation: 2387

Seems to me it can be easily achieved by next query:

update db2.A a2, db1.A a1 set a2.email=a1.email where a1.id=a2.id and (db2.email is null or db2.email = '') and a1.email <> a2.email;

thanks everyone.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want an update statement.

Mysql uses a slightly non-standard syntax for update (which I have a tendency to forget). I think the right syntax is:

update db2
    from db1
    set db2.email = db1.email
    where db1.id = db2.id and (db2.email is null or db2.email <> db1.email)

Upvotes: 1

jcho360
jcho360

Reputation: 3759

Did you try with the is not null condition?

SELECT a.*, b.* 
from db1.A a1 
left join db2.A a2 
using(id)
 where a1.email <> a2.email
and (a1.mail is not null and a2.mail is not)

Upvotes: 0

aF.
aF.

Reputation: 66687

Use a ISNULL expression! It uses the second argument when the first is NULL.

Something like this:

SELECT a.*, ISNULL(b.columnName, a.columnName) as 'columnName'
from db1.A a1
left join db2.A a2 using(id)
where a1.email <> a2.email

You'll have to replace the columnName by the real column names, and do it for every column that you want.

Upvotes: 1

Related Questions