Advertisement
Guest User

Untitled

a guest
Feb 21st, 2019
60
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.88 KB | None | 0 0
  1. CREATE TABLE t5 (
  2. col1 VARCHAR(36) NOT NULL PRIMARY KEY,
  3. col2 int(11) DEFAULT NULL,
  4. col3 int(11) DEFAULT NULL,
  5. UNIQUE (col2)
  6. );
  7.  
  8. insert into t5(col1, col2, col3) values
  9. ('872c18ba-35ae-11e9-91a5-88d7f65253d0', 1, 1),
  10. ('823c3d3a-35ae-11e9-91a5-88d7f65253d0', 2, 2);
  11.  
  12. insert into t5(col1, col2, col3) values
  13. ('872c18ba-35ae-11e9-91a5-88d7f65253d0', 2, 888),
  14. ('823c3d3a-35ae-11e9-91a5-88d7f65253d0', NULL, 999),
  15. on duplicate key update
  16. col2=if(values(col2) IS NULL, col2, values(col2)),
  17. col3=if(values(col3) IS NULL, col3, values(col3));
  18.  
  19. update t5 set
  20. col2=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 2 END)
  21. col3=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 888 when '823c3d3a-35ae-11e9-91a5-88d7f65253d0' then 999 END)
  22. where col1 in ('872c18ba-35ae-11e9-91a5-88d7f65253d0', '823c3d3a-35ae-11e9-91a5-88d7f65253d0');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement