CODEWITHSUNDEEP
mysqlsql
Hasan Yilmaz
Hasan Yilmaz

Reputation: 118

how to update a table column with values within?

i need to update a column with some cells are full and some of are blank. i have a code like this and it is not working.

UPDATE table_name
SET    t1.type_name = t2.type_name
FROM   table_name t1
INNER JOIN (
    SELECT DISTINCT progID, type_name 
    FROM table_name 
    WHERE type_name <>''
) t2 ON t1.progID = t2.progID
WHERE t1.type_name <>''

Upvotes: 0

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I would be wary of using select distinct in the subquery because you can still get duplicates. Just use group by, so you are guaranteed that the subquery generates only one row per ProgId:

UPDATE table_name t JOIN
       (SELECT progID, MAX(type_name) as type_name
        FROM table_name 
        WHERE type_name <> ''
        GROUP BY progID
       ) tt
       ON t.progID = tt.progID
    SET t.type_name = tt.type_name
FROM table_name t1
WHERE t1.type_name <> '';

Upvotes: 0

forpas
forpas

Reputation: 164089

This is the correct syntax:

UPDATE table_name t1 
INNER JOIN (select distinct progID, type_name from table_name where type_name <>'') t2 
ON t1.progID = t2.progID 
SET t1.type_name = t2.type_name
WHERE t1.type_name <> ''

You don't need the FROM clause.
But are you sure that you want to update the type_name column that has a value?
Maybe change the last condition to:

WHERE t1.type_name IS NULL

Upvotes: 2

Related Questions