CODEWITHSUNDEEP
mysqlsqlsql-update
vulkanino
vulkanino

Reputation: 9134

SQL to update a table with rows in the same table but with different criteria

I have a table like so:

business_positions
id   code   description_1   description_2

For some reason it is wrongly populated for the first half with the description_1 column filled in and the description_2 column empty, and the second half is the opposite:

id  |code|description_1|description_2
 900|   1|Senior Manager|(empty)
 901|   1|President|(empty)
 902|   1|Board Member|(empty)
 903|   2|Another Description|(empty)
...
2000|   1|(empty)|Desc
2001|   1|(empty)|Desc2
2002|   1|(empty)|Desc3
2003|   2|(empty)|Desc4
...

I want to update the table to have both the descriptions filled in the same row, matching rows through the code field and respecting the insertion order (ie no sorting). The ids are always unique. The result I am after is:

id  |code|description_1|description_2
 900|   1|Senior Manager|Desc
 901|   1|President|Desc2
 902|   1|Board Member|Desc3
 903|   2|Another Description|Desc4

and then delete the second half, with ids from 2000 on.

How do I do that?

Tried this with no luck:

UPDATE 
    business_positions A
LEFT JOIN business_positions B ON (A.code=B.code )
SET
    A.description_2 = B.description_2
WHERE
    A.description_2=''
AND
    B.description_2<>'' 
AND 
    A.id<>B.id

Upvotes: 0

Views: 63

Answers (2)

Fathah Rehman P
Fathah Rehman P

Reputation: 8761

Use following query to update

update business_positions t1 inner join business_positions t2
on t2.id=t1.id+1100
 set t1.description_2=t2.description_2

Then delete unwanted rows using following query

delete from business_positions where id>1999;

Upvotes: 1

Mohit
Mohit

Reputation: 17

Try this

;with CTE  
AS  
(  
    select description_2 from business_positions where description_1 = " "  
)  

update A  
Set description_2 = B.description_2  
FROM business_positions A  
INNER JOIN CTE B  
ON A.description_2 = " "  

DELETE business_positions  
where description_1 = " "  


select * From business_positions

Upvotes: 0

Related Questions