Advertisement
Guest User

Untitled

a guest
Jul 19th, 2019
86
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.66 KB | None | 0 0
  1. --step one (no rows in original table shoud be affected.
  2. CREATE TABLE table3_data_feed_items AS
  3. SELECT
  4. id ,
  5. regexp_replace(offer_id, '([\x0000-\x001f])', '', 'g') as offer_id ,
  6. regexp_replace(offer_title, '([\x0000-\x001f])', '', 'g') as offer_title,
  7. regexp_replace(offer_description, '([\x0000-\x001f])', '', 'g') as offer_description ,
  8. regexp_replace(manufacturer, '([\x0000-\x001f])', '', 'g') as manufacturer ,
  9. price ,
  10. shipping_rate ,
  11. source ,
  12. regexp_replace(sub_category, '([\x0000-\x001f])', '', 'g') as sub_category ,
  13. regexp_replace(category, '([\x0000-\x001f])', '', 'g') as category ,
  14. regexp_replace(merchant, '([\x0000-\x001f])', '', 'g') as merchant ,
  15. regexp_replace(image_url, '([\x0000-\x001f])', '', 'g') as image_url ,
  16. original_price
  17. from data_feed_items
  18. where
  19. offer_description ~* '([\x0000-\x001f])'
  20. or offer_title ~* '([\x0000-\x001f])'
  21. or offer_id ~* '([\x0000-\x001f])'
  22. or manufacturer ~* '([\x0000-\x001f])'
  23. or sub_category ~* '([\x0000-\x001f])'
  24. or category ~* '([\x0000-\x001f])'
  25. or merchant ~* '([\x0000-\x001f])'
  26. or image_url ~* '([\x0000-\x001f])'
  27. ;
  28.  
  29. update data_feed_items a
  30. set
  31.  
  32. offer_id=b.offer_id,offer_title=b.offer_title,offer_description=b.offer_description,manufacturer=b.manufacturer,sub_category=b.sub_category,category=b.category,merchant=b.merchant,
  33. image_url=b.image_url
  34.  
  35. from
  36. table3_data_feed_items b
  37. where a.id=b.id and
  38. (a.offer_id!=b.offer_id or a.offer_title!=b.offer_title or a.offer_description!=b.offer_description or a.manufacturer!=b.manufacturer or a.sub_category!=b.sub_category or a.category!=b.category or a.merchant!=b.merchant or
  39. a.image_url!=b.image_url) ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement