CODEWITHSUNDEEP
mysqlsql
lingo
lingo

Reputation: 1908

Updating values in one table based on another

I have following UPDATE query, but something is wrong when executing. I'm trying to update values in table1 with values in table import_data, but I need the id from table ids... So the table ids links table1 and *import Simple, isn't it?

UPDATE table1
INNER JOIN import_data ON (
    import_data.id = (
        SELECT customer_id 
        FROM ids
        INNER JOIN table1 ON (
            table1.id = ids.link
        )
        WHERE ids.type = 'NORMAL'
    )
)
SET table1.name = import_data.name;

table1

| id       | name      |
|----------|-----------|
| AA-00001 | Company 1 |
| AA-00002 | Company 2 |
| AA-00003 | Company 3 |

import_data

| id     | name              |
|--------|-------------------|
| 401000 | Updated company 1 |
| 401001 | Updated company 2 |
| 401002 | Company 3         |

ids

| link     | id     | type   |
|----------|--------|--------|
| AA-00001 | 401000 | NORMAL |
| AA-00002 | 401001 | NORMAL |
| AA-00003 | 401002 | NORMAL |

Upvotes: 1

Views: 57

Answers (3)

Utsav
Utsav

Reputation: 8143

I dont have mysql to check it, but it should work the way you want. Please check.

               UPDATE table1
                INNER JOIN ids
                ON table1.id= ids.link
                INNER JOIN import_data 
                on import_data.id = ids.id
                SET table1.name = import_data.name
                WHERE ids.type = 'NORMAL';

Upvotes: 0

Ranjana
Ranjana

Reputation: 825

I think it's depends on database you're using. The relevant post is answered here - How can I do an UPDATE statement with JOIN in SQL?

Upvotes: 0

Praveen
Praveen

Reputation: 9355

Try:

update table1 t1
inner join (
    select idata.name, ids.link
    from import_data idata
    join ids on ids.id = idata.id
    where ids.type = 'NORMAL'
) x
on x.link = t1.id
set t1.name = x.name

Demo sqlfiddle

Upvotes: 1

Related Questions