Reputation: 9134
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
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
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